詳解SQL對Xml欄位的操作範例程式碼（圖）

 

T-Sql操作Xml資料

一、前言

SQL Server 2005 引進了一種稱為XML 的本機資料型別。使用者可以建立這樣的表，它在關係列之外還有一個或多個 XML 類型的列；此外，還允許帶有變數和參數。為了更好地支援 XML 模型特徵（例如文件順序和遞歸結構），XML 值以內部格式儲存為大型二進位物件 (BLOB)。

用戶將一個XML資料存入資料庫的時候，可以使用這個XML的字串，SQL Server會自動的將這個字串轉換成XML類型，並儲存到資料庫中。

隨著SQL Server 對XML欄位的支持，對應的，T-SQL語句也提供了大量對XML操作的功能來配合SQL Server中XML欄位的使用。本文主要說明如何使用SQL語句對XML進行操作。

二、定義XML欄位

在進行資料庫的設計中，我們可以在表格設計器中，很方便的將一個欄位定義為XML類型。需要注意的是，XML欄位不能用來作為主鍵或索引鍵。同樣，我們也可以使用SQL語句來建立使用XML欄位的資料表，而下面的語句會建立一個名為“docs”的表，該表帶有整數主鍵“pk”和非類型化的XML 欄位“xCol” ：

CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)

XML類型除了在表中使用，還可以在預存程序、事務、函數等中出現。下面我們來完成我們對XML操作的第一步，使用SQL語句定義一個XML類型的數據，並為它賦值：

declare @xmlDoc xml;
set @xmlDoc=&#39;<book id="0001">
<title>C Program</title>
<author>David</author>
<price>21</price>
</book>&#39;

三、查詢操作

在定義了一個XML類型的資料之後，我們最常用的就是查詢操作，下面我們來介紹如何使用SQL語句來進行查詢操作的。

在T-Sql中，提供了兩個對XML類型資料進行查詢的函數，分別是query(xquery)和value(xquery, dataType)，其中，query(xquery)得到的是帶有標籤的數據，而value(xquery, dataType)得到的則是標籤的內容。接下類別我們分別使用這兩個函數來進行查詢。

1、使用query(xquery) 查詢

我們需要得到書的標題（title），使用query(xquery)來查詢，查詢語句為：

select @xmlDoc.query('/book/title')

執行結果如圖：

#2##、使用value(xquery , dataType) 查詢

同樣是得到書的標題，使用value函數，需要指明兩個參數，一個為xquery， 另一個為得到資料的型別。看下面的查詢語句：

select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')

執行結果如圖：

#3##、查詢屬性值無論是使用query或value，都可以很容易的得到一個節點的某個屬性值，例如，我們很希望得到book節點的id，我們這裡使用value方法進行查詢，語句為：

select @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')

運行結果如圖：

4

、使用xpath進行查詢 xpath是.net平台下支援的，統一的Xml查詢語句。使用XPath可以方便的得到想要的節點，而不用使用where語句。例如，我們在@xmlDoc中加入了另外一個節點，重新定義如下：

set @xmlDoc=&#39;<root>
<book id="0001">
<title>C# Program</title>
<author>Jerry</author>
<price>50</price>
</book>
<book id="0002">
<title>Java Program</title>
<author>Tom</author>
<price>49</price>
</book>
</root>&#39;

--得到id為0002的book節點

select @xmlDoc.query('(/root/book[@id="0002"])')

上面的語句可以獨立運行，它得到的是id為0002的節點。運行結果如下圖：

四、修改操作

SQL的修改操作包含更新與刪除。 SQL提供了modify()方法，實現Xml的修改操作。 modify方法的參數為XML修改語言。 XML修改語言類似SQL 的Insert、Delete、UpDate，但不一樣。

1

、修改節點值我們希望將id為0001的書的價錢(price)修改為100， 我們就可以使用modify方法。程式碼如下：

set @xmlDoc.modify('replace value of (/root/book[@id=0001]/price/text())[1] with "100"')
--得到id为0001的book节点
select @xmlDoc.query('(/root/book[@id="0001"])')

注意：modify方法必須出現在set的後面。運行結果如圖：

2

、刪除節點接下來我們來刪除id為0002的節點，程式碼如下：

--删除节点id为0002的book节点
set @xmlDoc.modify('delete /root/book[@id=0002]')
select @xmlDoc

運行結果如圖：

3、添加节点

很多时候，我们还需要向xml里面添加节点，这个时候我们一样需要使用modify方法。下面我们就向id为0001的book节点中添加一个ISBN节点，代码如下：

--添加节点

set @xmlDoc.modify(&#39;insert <isbn>78-596-134</isbn> before (/root/book[@id=0001]/price)[1]&#39;)
select @xmlDoc.query(&#39;(/root/book[@id="0001"]/isbn)&#39;)

运行结果如图：

4、添加和删除属性

当你学会对节点的操作以后，你会发现，很多时候，我们需要对节点进行操作。这个时候我们依然使用modify方法，例如，向id为0001的book节点中添加一个date属性，用来存储出版时间。代码如下：

--添加属性

set @xmlDoc.modify('insert attribute date{"2008-11-27"} into (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')

运行结果如图：

如果你想同时向一个节点添加多个属性，你可以使用一个属性的集合来实现，属性的集合可以写成：(attribute date{"2008-11-27"}, attribute year{"2008"})，你还可以添加更多。这里就不再举例了。

5、删除属性

删除一个属性，例如删除id为0001 的book节点的id属性，我们可以使用如下代码：

--删除属性

set @xmlDoc.modify('delete root/book[@id="0001"]/@id')
select @xmlDoc.query('(/root/book)[1]')

运行结果如图：

6、修改属性

修改属性值也是很常用的，例如把id为0001的book节点的id属性修改为0005，我们可以使用如下代码：

--修改属性

set @xmlDoc.modify('replace value of (root/book[@id="0001"]/@id)[1] with "0005"')
select @xmlDoc.query('(/root/book)[1]')

运行结果如图：

OK，经过上面的学习，相信你已经可以很好的在SQL中使用Xml类型了，下面是我们没有提到的，你可以去其它地方查阅：exist()方法，用来判断指定的节点是否存在，返回值为true或false； nodes()方法，用来把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行。

 

以上是詳解SQL對Xml欄位的操作範例程式碼（圖）的詳細內容。更多資訊請關注PHP中文網其他相關文章！