SQL2008 詳解直接將XML存入到SQL中

一、前言

從 SQL Server 2005 開(kāi)始，就增加了 xml 字段類(lèi)型，也就是說(shuō)可以直接把 xml 內(nèi)容存儲(chǔ)在該字段中，并且 SQL Server 會(huì)把它當(dāng)作 xml 來(lái)對(duì)待，而不是當(dāng)作 varchar 來(lái)對(duì)待。

隨著SQL Server 對(duì)XML字段的支持，相應(yīng)的，T-SQL語(yǔ)句也提供了大量對(duì)XML操作的功能來(lái)配合SQL Server中XML字段的使用。本文主要說(shuō)明如何使用SQL語(yǔ)句對(duì)XML進(jìn)行操作。

二、定義XML字段

          在進(jìn)行數(shù)據(jù)庫(kù)的設(shè)計(jì)中，我們可以在表設(shè)計(jì)器中，很方便的將一個(gè)字段定義為XML類(lèi)型。需要注意的是，XML字段不能用來(lái)作為主鍵或者索引鍵。同樣，我們也可以使用SQL語(yǔ)句來(lái)創(chuàng)建使用XML字段的數(shù)據(jù)表，下面的語(yǔ)句創(chuàng)建一個(gè)名為“docs”的表，該表帶有整型主鍵“pk”和非類(lèi)型化的 XML 列“xCol”：

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

XML類(lèi)型除了在表中使用，還可以在存儲(chǔ)過(guò)程、事務(wù)、函數(shù)等中出現(xiàn)。下面我們來(lái)完成我們對(duì)XML操作的第一步，使用SQL語(yǔ)句定義一個(gè)XML類(lèi)型的數(shù)據(jù)，并為它賦值：

set @xmlDoc='?xml version="1.0" ?>

    books>

       book id="0001">

title>C Program/title>

author>David/author>

price>21/price>

 /book>

 book id="0002">

title>你必須知道的.NET/title>

author>王濤/author>

price>79/price>

 /book>

  /books>'

select @xmlDoc 

三、XML字段注意點(diǎn)

三、XML字段注意點(diǎn)

• SQL Server 中以 Unicode(UTF-16) 來(lái)存儲(chǔ) XML 數(shù)據(jù)。
• XML 字段最多可存儲(chǔ) 2G 的數(shù)據(jù)。
• 可以像插入字符串一樣向 XML 字段寫(xiě)入內(nèi)容。
• 當(dāng)在 xml 數(shù)據(jù)類(lèi)型實(shí)例中存儲(chǔ) XML 數(shù)據(jù)時(shí)，不會(huì)保留 XML 聲明（如 ?xml version='1.0'?>）。
• 插入的 xml 內(nèi)容的屬性的順序可能會(huì)與原 xml 實(shí)例的順序變化。
• 不保留屬性值前后的單引號(hào)和雙引號(hào)。
• 不保留命名空間前綴。
• 可以對(duì) XML 字段中的 XML 內(nèi)容建立索引。
• 可以對(duì) XML 字段中的 XML 內(nèi)容建立約束，比如 age 節(jié)點(diǎn)必須大于等于 18。
• 可以通過(guò)創(chuàng)建架構(gòu)來(lái)對(duì) XML 進(jìn)行類(lèi)型化，比如讓 xml 內(nèi)容的 user> 節(jié)點(diǎn)下面必須有 fullname> 節(jié)點(diǎn)。
  

四、查詢(xún)操作

在定義了一個(gè)XML類(lèi)型的數(shù)據(jù)之后，我們最常用的就是查詢(xún)操作，下面我們來(lái)介紹如何使用SQL語(yǔ)句來(lái)進(jìn)行查詢(xún)操作的。

          在T-Sql中，提供了兩個(gè)對(duì)XML類(lèi)型數(shù)據(jù)進(jìn)行查詢(xún)的函數(shù)，分別是query(xquery)和value(xquery, dataType)，其中，query(xquery)得到的是帶有標(biāo)簽的數(shù)據(jù)，而value(xquery, dataType)得到的則是標(biāo)簽的內(nèi)容。接下類(lèi)我們分別使用這兩個(gè)函數(shù)來(lái)進(jìn)行查詢(xún)。

1、使用query(xquery) 查詢(xún)

我們需要得到書(shū)的標(biāo)題（title），使用query(xquery)來(lái)進(jìn)行查詢(xún)，查詢(xún)語(yǔ)句為：

select @xmlDoc.query('(books/book/title)[1]')

運(yùn)行結(jié)果如圖：

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

同樣是得到書(shū)的標(biāo)題，使用value函數(shù)，需要指明兩個(gè)參數(shù)，一個(gè)為xquery， 另一個(gè)為得到數(shù)據(jù)的類(lèi)型。看下面的查詢(xún)語(yǔ)句：

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

運(yùn)行結(jié)果如圖：

3、查詢(xún)屬性值

無(wú)論是使用query還是value，都可以很容易的得到一個(gè)節(jié)點(diǎn)的某個(gè)屬性值，例如，我們很希望得到book節(jié)點(diǎn)的id，我們這里使用value方法進(jìn)行查詢(xún)，語(yǔ)句為：

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

運(yùn)行結(jié)果如圖：

4、使用xpath進(jìn)行查詢(xún)

xpath是.net平臺(tái)下支持的，統(tǒng)一的Xml查詢(xún)語(yǔ)句。使用XPath可以方便的得到想要的節(jié)點(diǎn)，而不用使用where語(yǔ)句。例如，

--得到id為0002的book節(jié)點(diǎn)

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

上面的語(yǔ)句可以獨(dú)立運(yùn)行，它得到的是id為0002的節(jié)點(diǎn)。運(yùn)行結(jié)果如下

五、修改操作

         SQL的修改操作包括更新和刪除。SQL提供了modify()方法，實(shí)現(xiàn)對(duì)Xml的修改操作。modify方法的參數(shù)為XML修改語(yǔ)言。XML修改語(yǔ)言類(lèi)似于SQL 的Insert、Delete、UpDate，但并不一樣。

1、修改節(jié)點(diǎn)值

我們希望將id為0001的書(shū)的價(jià)錢(qián)(price)修改為100， 我們就可以使用modify方法。代碼如下：

set @xmlDoc.modify('replace value of (/books/book[@id=0001]/price/text())[1] with "100"')

--得到id為0001的book節(jié)點(diǎn)

select @xmlDoc.query('(/books/book[@id="0001"])')

注意：modify方法必須出現(xiàn)在set的后面。運(yùn)行結(jié)果如圖：

2、刪除節(jié)點(diǎn)

接下來(lái)我們來(lái)刪除id為0002的節(jié)點(diǎn)，代碼如下：

--刪除節(jié)點(diǎn)id為0002的book節(jié)點(diǎn)

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

select @xmlDoc

運(yùn)行結(jié)果如圖：

3、添加節(jié)點(diǎn)

很多時(shí)候，我們還需要向xml里面添加節(jié)點(diǎn)，這個(gè)時(shí)候我們一樣需要使用modify方法。下面我們就向id為0001的book節(jié)點(diǎn)中添加一個(gè)ISBN節(jié)點(diǎn)，代碼如下：

--添加節(jié)點(diǎn)

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

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

運(yùn)行結(jié)果如圖：

4、添加和刪除屬性

當(dāng)你學(xué)會(huì)對(duì)節(jié)點(diǎn)的操作以后，你會(huì)發(fā)現(xiàn)，很多時(shí)候，我們需要對(duì)節(jié)點(diǎn)進(jìn)行操作。這個(gè)時(shí)候我們依然使用modify方法，例如，向id為0001的book節(jié)點(diǎn)中添加一個(gè)date屬性，用來(lái)存儲(chǔ)出版時(shí)間。代碼如下：

--添加屬性

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

select @xmlDoc.query('(/books/book[@id="0001"])')

運(yùn)行結(jié)果如圖：

如果你想同時(shí)向一個(gè)節(jié)點(diǎn)添加多個(gè)屬性，你可以使用一個(gè)屬性的集合來(lái)實(shí)現(xiàn)，屬性的集合可以寫(xiě)成：(attribute date{"2008-11-27"}, attribute year{"2008"})，你還可以添加更多。這里就不再舉例了。

5、刪除屬性

刪除一個(gè)屬性，例如刪除id為0001 的book節(jié)點(diǎn)的id屬性，我們可以使用如下代碼：

--刪除屬性

set @xmlDoc.modify('delete books/book[@id="0001"]/@id')

select @xmlDoc.query('(/books/book)[1]')

運(yùn)行結(jié)果如圖：

6、修改屬性

修改屬性值也是很常用的，例如把id為0001的book節(jié)點(diǎn)的id屬性修改為0005，我們可以使用如下代碼：

--修改屬性

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

select @xmlDoc.query('(/books/book)[1]')

運(yùn)行結(jié)果如圖：

經(jīng)過(guò)上面的學(xué)習(xí)，相信你已經(jīng)可以很好的在SQL中使用Xml類(lèi)型了，下面是我們沒(méi)有提到的：exist()方法，用來(lái)判斷指定的節(jié)點(diǎn)是否存在，返回值為true或false； nodes()方法，用來(lái)把一組由一個(gè)查詢(xún)返回的節(jié)點(diǎn)轉(zhuǎn)換成一個(gè)類(lèi)似于結(jié)果集的表中的一組記錄行。 你可以去MSDN查閱  http://msdn.microsoft.com/zh-cn/library/ms190798.aspx。

感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！