淺析sql server對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類型的資料不能出現在等號的任何一邊。

大致可分為查詢類，修改類和跨域查詢類。

查詢類包含query(),value(),exist()和nodes().

修改類包含modify().

跨域查詢類包含sql:variable()和sql:column().


建立XML自訂資料庫表

建立xml自訂表格：以前在網上查的都是

declare @xmlDoc xml;

set @xmlDoc='<book id="0001">

<title>C Program</title>

<author>David</author>

<price>21</price>

</book>'  這樣的，但是這僅僅是學習，不能真正用在項目或實際中缺乏實踐性。因為很少有直接操作sql記憶體中的這些。

閑話少說，直接上SQL建立表語句

代碼如下

複製代碼

--1、建立xml測試資料庫表Xml_Table  Author:Fly , Email:feifei12300@126.com use Fly_Test --測試資料庫 go create table Xml_Table(ID  INT identity PRIMARY KEY, XmlData  XML); --2、插入測試資料 insert into Xml_Table(XmlData) values ('<book id="0001"> <title>SqlServer2005</title> <author>Fly</author> <price>21</price> </book> '); insert into Xml_Table(XmlData) values ('<book id="0002"> <title>SqlServer2008</title> <author>Fly</author> <price>22</price> </book> '); insert into Xml_Table(XmlData) values ('<book id="0003"> <title>SqlServer2012</title> <author>Fly</author> <price>23</price> </book> '); --3、查詢 select * from Xml_Table;

結果如圖：

 

對xml操作

對xml操作，也不做過多解析，如有不清晰的可以聯絡我；Emil:feifei12300@126.com

需要注意的是給每個節點添加屬性或者添加節點的時候如果已經存在的會報錯,所以最好是先exist('你的條件')=0 一下；

代碼如下

複製代碼

--4、對XML操作真正開始了 --SQLServer2005 中對 XML 的處理功能顯然增強了很多，提供了 query(),value(),exist(),modify(),nodes() --查詢所有書的名稱及作者 select XmlData.query('/book') as Title,XmlData.query('/book/author') as Author from Xml_Table; --顯然這不是我們想要的資料 select XmlData.value('(/book/title)[1]','nvarchar(max)') as Title,     XmlData.value('(/book/author)[1]','nvarchar(max)') as Author from Xml_Table; --查詢數目編號為0001的書的資訊 select  XmlData.value('(/book/title)[1]','nvarchar(max)') as Title,     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID from Xml_Table     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; --修改數目編號為0001 的價格為 11 update Xml_Table     set XmlData.modify('replace value of (/book[@id="0001"]/price/text())[1] with "11"'); --修改 所有的數目作者為Fly_12300 update Xml_Table     set XmlData.modify('replace value of (/book/author/text())[1] with "Fly_12300"') --查看是否編號為0001的價格修改為11,且所有作者修改為Fly_12300 select  XmlData.value('(/book/price)[1]','nvarchar(max)') as Title,     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,     XmlData.value('(/book/author)[1]','nvarchar(max)') as Author from Xml_Table     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; --添加屬性  update Xml_Table set XmlData.modify('insert attribute isbn {"12300321"} into (/book)[1]'); --查看是否存在屬性isbn select  XmlData.value('(/book/@isbn)[1]','nvarchar(max)') as isbn,     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID from Xml_Table     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; --在編號為0001的添加子節點 category  為 Computer 的分類 update Xml_Table     set XmlData.modify('insert <category>Computer</category> before (/book[@id=0001]/author)[1]'); --查看是否添加了category節點 select  XmlData.value('(/book/category)[1]','nvarchar(max)') as category,     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,XmlData from Xml_Table     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; --刪除節點 update Xml_Table     set XmlData.modify('delete /book[@id=0001]/category'); --查看是否刪除了category節點 select  XmlData.value('(/book/category)[1]','nvarchar(max)') as category,     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,XmlData from Xml_Table     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; --nodes()  查詢 book的編碼 select ids.value('@id', 'varchar(max)'),ids.value('(title)[1]','nvarchar(max)') title from  Xml_Table     CROSS APPLY XmlData.nodes('//book') as X(ids) ; --exist() select XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID     from Xml_Table     where XmlData.exist('(/book/@id)')=1 --判斷是否存在

如圖:

 

xml xpath

代碼如下

複製代碼

create table Books(ID nvarchar(32) not null,Name nvarchar(64)); insert into Books values ('0001','MSSQLServer2005'); --書名MSSQLServer2005 insert into Books values ('0002','MSSQLServer2008'); --書名MSSQLServer2008 insert into Books values ('0003','MSSQLServer2012'); --書名MSSQLServer2012 --以下為xml   path SELECT ID,NAME FROM [dbo].[Books] FOR XML AUTO; SELECT ID,NAME FROM [dbo].[Books] FOR XML AUTO ,ELEMENTS ,ROOT('books'); SELECT ID as 'BookID',NAME as 'BookName' FROM [dbo].[Books] FOR XML RAW; SELECT ID,NAME FROM [dbo].[Books] FOR XML RAW('book') ,ELEMENTS ,ROOT('books'); SELECT ID,NAME FROM [dbo].[Books] FOR XML PATH('') ; SELECT ID as 'Detail/@ID',NAME as 'Detail/Name' FROM [dbo].[Books] FOR XML PATH('Book'), ROOT('Books'); SELECT STUFF((SELECT ';' + Name FROM [dbo].[Books] FOR XML PATH('')),1,1,'');

如圖：

 

跨網域作業

代碼如下

複製代碼

--根據Books 表中的ID，Xml_Table 表中的XmlData ID屬性  修改對應的 title屬性 --即：根據在books中編碼0001的 的名稱 MSSQLServer2005 --修改為Xml_Table表中book編碼為0001的title為 MSSQLServer2005 declare @data xml declare @id nvarchar(36) declare @name nvarchar(64) declare custore_name cursor for select Books.ID,Xml_Table.XmlData,Books.Name from Books,Xml_Table     where Books.ID= Xml_Table.XmlData.value('(/book/@id)[1]','nvarchar(max)');  OPEN custore_name   FETCH NEXT FROM custore_name into @id, @data, @name WHILE(@@FETCH_STATUS=0)       BEGIN        set @data.modify(('replace value of (/book/title/text())[1] with sql:variable("@name")'))   update Xml_Table set XmlData = @data where XmlData.value('(/book/@id)[1]','nvarchar(max)') = @id    FETCH NEXT FROM custore_name into       @id, @data, @name  END     CLOSE custore_name   deallocate custore_name  select * from Xml_Table

如圖所示：

 

六、結束語

需要注意點：添加、修改屬性或者節點需要先判斷是否存在（exist）；跨網域作業時使用了遊標，不熟悉的可以自己查閱相關資料。


相關閱讀：SQL SERVER FOR XML PATH 行轉列執行個體詳解