SQL Server中操作XML型別資料

xml資料以其靈活的資料儲存方式受到廣大開發人員喜愛。在sqlserver2005中存在xml資料型別。我們可以使用這樣的語句對xml資料進行操作

首先定義一段xml 資料：

程式碼
declare @xmldata xml
set @xmldata = '


 
  Giada De Laurentiis
  2005
  30.00


 
  J K. Rowling
  2005
  29.99


 
  James McGovern
  Per Bothner
  Kurt Cagle
  James Linn
  Vaidyanathan Nagarajan
  2003
  49.99


 
  Erik T. Ray
  2003
  39.95

 

 

1、查詢：@xml . query(xpath) xpath 是匹配方式字串。必須是完整的，非拼接而成的字串。'.' 代表本身 '/'代表子節點 '//'代表後代 '*'代表任何

　　查詢bookstore下所有屬性category="WEB"的book節點 ：

　　

select @xmldata . query ('./bookstore/book[@category="WEB"]')
 

      查詢最後1個book 節點：

　　
select @xmldata . query ('./bookstore/book[last()]')
 

　　　　中括號中可以是數字，表示第幾個節點。注意：這裡的數字1表示第一個節點，爾非0表示第一個。也可以是這樣：[position()<=2] 表示前2個節點

　　查詢所有存在lang屬性的節點：（查詢所有後代節點中包含屬性lang的節點）

 　　

select @xmldata . query ('//*[@lang]')
 

　　　　同時，查詢可以包含條件:  查詢所有price節點值大於30的price節點

 

select @xmldata . query ('//price[text() > 30]')
 

　　　　或則更復雜的條件： 查詢所有的book節點，條件是其子節點的title節點的lang屬性值為"en" 並且 year節點的值為"2003"

 

select @xmldata . query ('//book[./title[@lang="en"] and ./year[text() = "2003"]]')
 

有時查詢字串可能包含一些引數：如下程式碼：我們可能期望通過這樣的語句查詢所有屬性category="WEB"的book節點

 

declare @parm varchar(3)
set @parm = "WEB"
select @xmldata . query ('//book[@category = "'+@parm+'"]')
 

但是，這樣是錯誤的。會返回這樣的錯誤：xml 資料型別方法 "query" 的引數 1 必須是字串文字。

通過:sql:variable("@parm")就能解決這樣的問題：

 

select @xmldata . query ('//book[@category = sql:variable("@parm")]')
 

另外還存在這樣的查詢方式：

 

select @xmldata . query ('for $b in bookstore/book where $b/year[text() = "2003"] return ($b)')
 

 

select @xmldata . query ('for $b in bookstore/book where $b/author order by $b/price[1] descending return ($b)')
 

注意：第二行程式碼 order by 語句後必須是精確的列，所以要使用[1]。如若不然，如果$b下存在多個price節點，這樣的查詢則不是我們所需要的

　　除 @xml . query(xpath) 方法外還存在

　　　　　　 . exist(xpath) 方法 ：

　　　　　　 　　返回0或1，表示xpath所選擇的內容是否存在

　　　　　    . value(xpath,type):

　　　　　　　　返回xpath所選擇內容的值，在這裡需要制定返回值的型別

為了使XML查詢更加高效，我們可以在XML資料上建立索引，具體方法暫不記錄。

 2、修改：@xml . modify(insert) | .modify(replace) | .modify(delete)

  　　將一段xml片段插入到制定節點後（前）after | before 

set @xmldata . modify('insert Hello after (//book)[last()]')
       修改掉price="39.95"的price節點的值為"50.00"

set @xmldata . modify('replace value of (/bookstore/book/price[text()="39.95"]/text())[1] with "50.00"')
      刪除所有test節點

set @xmldata . modify('delete //test')
 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-622496/，如需轉載，請註明出處，否則將追究法律責任。