SQL Server 2005及以上版本中XML操作函數詳解打賞

一直以來,使用sqlserver都比較少,之前讀書時學過sqlserver2000、sqlserver2005,卻一直沒接觸到類似于sqlserver2005中引入新的字段類型XML、varchar(max)、nvarchar(max)、varbinary(max)之類的知識。直到今天,用到大字節的數據存儲,才發現有這個改進,確切點說,是sqlserver2005中廢棄了text、ntext 和image類型采用新的max標記來擴展原有字段的存儲能力,如varchar(max)、nvarchar(max)、varbinary(max),關于這些在后面單獨描述,本篇介紹一下sqlserver2005以后新增的一個類型——XML。

我們采用T-Sql操作Xml數據。使用sqlserver2005引入的XML數據類型。用戶可以創建這樣的表,它在關系列之外還有一個或多個 XML 類型的列;此外,還允許帶有變量和參數。為了更好地支持 XML 模型特征(例如文檔順序和遞歸結構),XML 值以內部格式存儲為大型二進制對象 (BLOB)。

用戶將一個XML數據存入數據庫的時候,可以使用這個XML的字符串,SQL Server會自動的將這個字符串轉化為XML類型,并存儲到數據庫中。隨著SQL Server 對XML字段的支持,相應的,T-SQL語句也提供了大量對XML操作的功能來配合SQL Server中XML字段的使用。本文主要說明如何使用SQL語句對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='<book id="0001">
<title>C Program</title>
<author>David</author>
<price>21</price>
</book>'

在定義了一個XML類型的數據之后,我們最常用的就是查詢操作,下面我們來介紹如何使用SQL語句來進行查詢操作的。首先要明確一個基本原則,XML類型的數據之間以及XML類型與其它數據類型之間都是不能比較的,也就是說XML類型的數據不能出現在等號的任何一邊。大致可分為查詢類,修改類和跨域查詢類。

查詢類包含:query(),value(),exist()和nodes().
修改類包含:modify().
跨域查詢類包含:sql:variable()和sql:column().

查詢類

query()方法:返回滿足條件的所有XML行。只能用于SELECT子句當中。
value()方法:返回從XML節點中提取的標量值。必須在value()方法的第二個參數中指定所返回的標量的數據類型,所以value()方法可以與其它標量進行比較。可用于SELECT子句和WHERE子句。
exist()方法:返回int型標量的0或者1。對每行的XML數據類型進行存在性檢查。可用于SELECT子句和WHERE子句。
nodes()方法:返回只有一個欄位的table,且該table的欄位是XML數據類型。所以nodes()方法只能出現在FROM子句中。

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

1、使用query(xquery) 查詢

我們需要得到書的標題(title),使用query(xquery)來進行查詢,查詢語句為:

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

運行結果如圖:
sqlserverxml1

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

同樣是得到書的標題,使用value函數,需要指明兩個參數,一個為xquery, 另一個為得到數據的類型。看下面的查詢語句:

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

運行結果如圖:
sqlserverxml2

3、查詢屬性值

無論是使用query還是value,都可以很容易的得到一個節點的某個屬性值,例如,我們很希望得到book節點的id,我們這里使用value方法進行查詢,語句為:

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

運行結果如圖:
sqlserverxml3

4、使用xpath進行查詢

xpath是.net平臺下支持的,統一的Xml查詢語句。使用XPath可以方便的得到想要的節點,而不用使用where語句。例如,我們在@xmlDoc中添加了另外一個節點,重新定義如下:

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節點

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

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

修改類

modify()方法:允許修改XML實例的某些部分,例如添加或刪除子樹,或者更新標量值(如將書的價格從9.99替換為39.99)。無返回值,只能用于SET子句中。

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的后面。運行結果如圖:
sqlserverxml5

2、刪除節點

接下來我們來刪除id為0002的節點,代碼如下:
刪除節點id為0002的book節點

set @xmlDoc.modify('delete /root/book[@id=0002]')
select @xmlDoc

運行結果如圖:
sqlserverxml6

3、添加節點

很多時候,我們還需要向xml里面添加節點,這個時候我們一樣需要使用modify方法。下面我們就向id為0001的book節點中添加一個ISBN節點,代碼如下:
添加節點

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

運行結果如圖:
sqlserverxml7

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"])')

運行結果如圖:
sqlserverxml8
如果你想同時向一個節點添加多個屬性,你可以使用一個屬性的集合來實現,屬性的集合可以寫成:(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]')

運行結果如圖:
sqlserverxml9

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]')

運行結果如圖:
sqlserverxml10

跨域查詢類

sql:variable()方法:可以在XQuery或XML DML表達式中應用SQL變量的值。

如果數據駐留在關系數據類型的列和XML數據類型的列的組合中,就可能需要編寫查詢來組合關系數據處理和XML數據處理(組合的對象還可以是標量數據類型的SQL變量和XML數據),這樣的查詢就叫跨域查詢。

sql:variable()方法:

DECLARE @isbn varchar(20)
SET     @isbn = '0-7356-1588-2'
SELECT  xCol
FROM    docs
WHERE   xCol.exist ('/book[@ISBN = sql:variable("@isbn")]') = 1

sql:column()方法:可以在XQuery或XML DML表達式中使用來自關系列的值。

整理來源:http://youring2.cnblogs.com/&http://developer.51cto.com/art/200902/111125.htm

SQL Server 2005及以上版本中XML操作函數詳解
文章《SQL Server 2005及以上版本中XML操作函數詳解》二維碼
  • 微信打賞
  • 支付寶打賞

已有4條評論

  1. kitten0

    這個就會安裝一下

    2012-12-24 16:04 回復

(必填)

(必填)

(可選)

黑龙江22选5开奖