(轉載)sql server xml欄位的操作

標籤：

原文轉自：http://blog.csdn.net/hliq5399/article/details/8315373

 

今天用到sql server 的xml欄位,首先這個項目中的xml欄位都存為了ntext欄位,所以第一個操作 ntext 轉化為 xml 

以下摘自

http://blog.darkthread.net/blogs/darkthreadtw/archive/2008/09/18/sql2005-nvarchar-to-xml.aspx

 

XmlString是一個NText欄位，裡面放存放的內容來自*.xml的讀取內容，試著將XmlStringCol轉成XML會發生錯誤:

SELECT TOP 1 CONVERT(XML, XmlString) FROM myTable

Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character 38, unable to switch the encoding

經過一番測試，總算明白問題出在哪了。從*.xml中讀出的內容，第一列放的是XML宣告<?xml version="1.0" encoding="UTF-8"?>，而記得嗎? SQL中一向是用UCS-2儲存資料的，這形成一個矛盾: 明明Encoding是UCS-2，但XML內容卻又宣稱自己是UTF-8，導致了轉換失敗。

我想到的解決方法是去掉encoding屬性，讓SQL自行看著辦，但由於欄位是NText，必須要轉成NVarChar(MAX)才可動用Replace狸貓換太子，於是指令要改成:

SELECT TOP 1 CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), XmlString), ‘encoding="UTF-8"‘, ‘‘)) FROM myTable

 

上面的內容解釋的不錯,就不多廢話了,下面就是xml欄位的操作了

http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html

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=‘<book id="0001">

<title>C Program</title>

<author>David</author>

<price>21</price>

</book>‘

三、查詢操作

在定義了一個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=‘<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的節點。運行結果如：

四、修改操作

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(‘insert <isbn>78-596-134</isbn> before (/root/book[@id=0001]/price)[1]‘)

select @xmlDoc.query(‘(/root/book[@id="0001"]/isbn)‘)

運行結果

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 server xml欄位的操作