T-Sql 操作Xml數(shù)據(jù)
一、前言
SQL Server 2005 引入了一種稱為 XML 的本機數(shù)據(jù)類型。用戶可以創(chuàng)建這樣的表,它在關系列之外還有一個或多個 XML 類型的列;此外,還允許帶有變量和參數(shù)。為了更好地支持 XML 模型特征(例如文檔順序和遞歸結(jié)構(gòu)),XML 值以內(nèi)部格式存儲為大型二進制對象 (BLOB)。
用戶將一個XML數(shù)據(jù)存入數(shù)據(jù)庫的時候,可以使用這個XML的字符串,SQL Server會自動的將這個字符串轉(zhuǎn)化為XML類型,并存儲到數(shù)據(jù)庫中。
隨著SQL Server 對XML字段的支持,相應的,T-SQL語句也提供了大量對XML操作的功能來配合SQL Server中XML字段的使用。本文主要說明如何使用SQL語句對XML進行操作。
二、定義XML字段
在進行數(shù)據(jù)庫的設計中,我們可以在表設計器中,很方便的將一個字段定義為XML類型。需要注意的是,XML字段不能用來作為主鍵或者索引鍵。同樣,我們也可以使用SQL語句來創(chuàng)建使用XML字段的數(shù)據(jù)表,下面的語句創(chuàng)建一個名為“docs”的表,該表帶有整型主鍵“pk”和非類型化的 XML 列“xCol”:
CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)
XML類型除了在表中使用,還可以在存儲過程、事務、函數(shù)等中出現(xiàn)。下面我們來完成我們對XML操作的第一步,使用SQL語句定義一個XML類型的數(shù)據(jù),并為它賦值:
declare @xmlDoc xml;
set @xmlDoc='<book id="0001">
<title>C Program</title>
<author>David</author>
<price>21</price>
</book>'
三、查詢操作
在定義了一個XML類型的數(shù)據(jù)之后,我們最常用的就是查詢操作,下面我們來介紹如何使用SQL語句來進行查詢操作的。
在T-Sql中,提供了兩個對XML類型數(shù)據(jù)進行查詢的函數(shù),分別是query(xquery)和value(xquery, dataType),其中,query(xquery)得到的是帶有標簽的數(shù)據(jù),而value(xquery, dataType)得到的則是標簽的內(nèi)容。接下類我們分別使用這兩個函數(shù)來進行查詢。
1 、使用query(xquery) 查詢
我們需要得到書的標題(title),使用query(xquery)來進行查詢,查詢語句為:
select @xmlDoc.query('/book/title')
運行結(jié)果如圖:
2 、使用value(xquery, dataType) 查詢
同樣是得到書的標題,使用value函數(shù),需要指明兩個參數(shù),一個為xquery, 另一個為得到數(shù)據(jù)的類型。看下面的查詢語句:
select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')
運行結(jié)果如圖:
3 、查詢屬性值
無論是使用query還是value,都可以很容易的得到一個節(jié)點的某個屬性值,例如,我們很希望得到book節(jié)點的id,我們這里使用value方法進行查詢,語句為:
select @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')
運行結(jié)果如圖:
4 、使用xpath進行查詢
xpath是.net平臺下支持的,統(tǒng)一的Xml查詢語句。使用XPath可以方便的得到想要的節(jié)點,而不用使用where語句。例如,我們在@xmlDoc中添加了另外一個節(jié)點,重新定義如下:
set @xmlDoc='<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>'
--得到id為0002的book節(jié)點
select @xmlDoc.query('(/root/book[@id="0002"])')
上面的語句可以獨立運行,它得到的是id為0002的節(jié)點。運行結(jié)果如下圖:
四、修改操作
SQL的修改操作包括更新和刪除。SQL提供了modify()方法,實現(xiàn)對Xml的修改操作。modify方法的參數(shù)為XML修改語言。XML修改語言類似于SQL 的Insert、Delete、UpDate,但并不一樣。
1 、修改節(jié)點值
我們希望將id為0001的書的價錢(price)修改為100, 我們就可以使用modify方法。代碼如下:
set @xmlDoc.modify('replace value of (/root/book[@id=0001]/price/text())[1] with "100"')
--得到id為0001的book節(jié)點
select @xmlDoc.query('(/root/book[@id="0001"])')
注意:modify方法必須出現(xiàn)在set的后面。運行結(jié)果如圖:
2 、刪除節(jié)點
接下來我們來刪除id為0002的節(jié)點,代碼如下:
--刪除節(jié)點id為0002的book節(jié)點
set @xmlDoc.modify('delete /root/book[@id=0002]')
select @xmlDoc
運行結(jié)果如圖:
3 、添加節(jié)點
很多時候,我們還需要向xml里面添加節(jié)點,這個時候我們一樣需要使用modify方法。下面我們就向id為0001的book節(jié)點中添加一個ISBN節(jié)點,代碼如下:
--添加節(jié)點
set @xmlDoc.modify('insert <isbn>78-596-134</isbn> before (/root/book[@id=0001]/price)[1]')
select @xmlDoc.query('(/root/book[@id="0001"]/isbn)')
運行結(jié)果如圖:
4 、添加和刪除屬性
當你學會對節(jié)點的操作以后,你會發(fā)現(xiàn),很多時候,我們需要對節(jié)點進行操作。這個時候我們依然使用modify方法,例如,向id為0001的book節(jié)點中添加一個date屬性,用來存儲出版時間。代碼如下:
--添加屬性
set @xmlDoc.modify('insert attribute date{"2008-11-27"} into (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
運行結(jié)果如圖:
如果你想同時向一個節(jié)點添加多個屬性,你可以使用一個屬性的集合來實現(xiàn),屬性的集合可以寫成:(attribute date{"2008-11-27"}, attribute year{"2008"}),你還可以添加更多。這里就不再舉例了。
5 、刪除屬性
刪除一個屬性,例如刪除id為0001 的book節(jié)點的id屬性,我們可以使用如下代碼:
--刪除屬性
set @xmlDoc.modify('delete root/book[@id="0001"]/@id')
select @xmlDoc.query('(/root/book)[1]')
運行結(jié)果如圖:
6 、修改屬性
修改屬性值也是很常用的,例如把id為0001的book節(jié)點的id屬性修改為0005,我們可以使用如下代碼:
--修改屬性
set @xmlDoc.modify('replace value of (root/book[@id="0001"]/@id)[1] with "0005"')
select @xmlDoc.query('(/root/book)[1]')
運行結(jié)果如圖:
OK,經(jīng)過上面的學習,相信你已經(jīng)可以很好的在SQL中使用Xml類型了,下面是我們沒有提到的,你可以去其它地方查閱:exist()方法,用來判斷指定的節(jié)點是否存在,返回值為true或false; nodes()方法,用來把一組由一個查詢返回的節(jié)點轉(zhuǎn)換成一個類似于結(jié)果集的表中的一組記錄行。
?
?
作者:齊飛
日期:2008年11月27日
參考資料:SQL Server聯(lián)機叢書、MSDN、《數(shù)據(jù)庫與SQL Server 2005教程》(清華大學出版社,錢雪忠編著)
版權(quán)歸作者所有,轉(zhuǎn)載請注明出處。
?
2010年9月24日更新,修改了內(nèi)容的顯示方式。
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
