sql server中對xml進(jìn)行操作

2024-08-31 00:55:05

字體：大中小

供稿：網(wǎng)友

sql server中對xml進(jìn)行操作一、前言

SQL Server 2005 引入了一種稱為 XML 的本機(jī)數(shù)據(jù)類型。用戶可以創(chuàng)建這樣的表，它在關(guān)系列之外還有一個(gè)或多個(gè) XML 類型的列；此外，還允許帶有變量和參數(shù)。為了更好地支持 XML 模型特征（例如文檔順序和遞歸結(jié)構(gòu)），XML 值以內(nèi)部格式存儲(chǔ)為大型二進(jìn)制對象 (BLOB)。

用戶將一個(gè)XML數(shù)據(jù)存入數(shù)據(jù)庫的時(shí)候，可以使用這個(gè)XML的字符串，SQL Server會(huì)自動(dòng)的將這個(gè)字符串轉(zhuǎn)化為XML類型，并存儲(chǔ)到數(shù)據(jù)庫中。

隨著SQL Server 對XML字段的支持，相應(yīng)的，T-SQL語句也提供了大量對XML操作的功能來配合SQL Server中XML字段的使用。本文主要說明如何使用SQL語句對XML進(jìn)行操作。（以上摘自Qi Fei's Blog）

首先要明確一個(gè)基本原則，XML類型的數(shù)據(jù)之間以及XML類型與其它數(shù)據(jù)類型之間都是不能比較的，也就是說XML類型的數(shù)據(jù)不能出現(xiàn)在等號(hào)的任何一邊。

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

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

修改類包含modify().

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

二、創(chuàng)建XML自定義數(shù)據(jù)庫表

創(chuàng)建xml自定義表：以前在網(wǎng)上查的都是

declare @xmlDoc xml;

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

<title>C PRogram</title>

<author>David</author>

</book>' 這樣的，但是這僅僅是學(xué)習(xí)，不能真正用在項(xiàng)目或?qū)嶋H中缺乏實(shí)踐性。因?yàn)楹苌儆兄苯硬僮鱯ql內(nèi)存中的這些。

閑話少說，直接上SQL創(chuàng)建表語句

 1 --1、創(chuàng)建xml測試數(shù)據(jù)庫表Xml_Table  Author:Fly , Email:feifei12300@126.com 2 use Fly_Test --測試數(shù)據(jù)庫 3 go 4 create table Xml_Table(ID  INT identity PRIMARY KEY, XmlData  XML); 5 --2、插入測試數(shù)據(jù) 6 insert into Xml_Table(XmlData) values 7 ('<book id="0001"> 8 <title>SqlServer2005</title> 9 <author>Fly</author>10 <price>21</price>11 </book>12 ');13 insert into Xml_Table(XmlData) values14 ('<book id="0002">15 <title>SqlServer2008</title>16 <author>Fly</author>17 <price>22</price>18 </book>19 ');20 insert into Xml_Table(XmlData) values21 ('<book id="0003">22 <title>SqlServer2012</title>23 <author>Fly</author>24 <price>23</price>25 </book>26 ');27 --3、查詢28 select * from Xml_Table;

結(jié)果如圖：

三、對xml操作

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

需要注意的是給每個(gè)節(jié)點(diǎn)添加屬性或者添加節(jié)點(diǎn)的時(shí)候如果已經(jīng)存在的會(huì)報(bào)錯(cuò),所以最好是先exist('你的條件')=0 一下；

 1 --4、對XML操作真正開始了 2 --SQLServer2005 中對 XML 的處理功能顯然增強(qiáng)了很多，提供了 query(),value(),exist(),modify(),nodes() 3 --查詢所有書的名稱及作者 4 select XmlData.query('/book') as Title,XmlData.query('/book/author') as Author from Xml_Table; 5 --顯然這不是我們想要的數(shù)據(jù) 6 select XmlData.value('(/book/title)[1]','nvarchar(max)') as Title,  7     XmlData.value('(/book/author)[1]','nvarchar(max)') as Author from Xml_Table; 8 --查詢數(shù)目編號(hào)為0001的書的信息 9 select  XmlData.value('(/book/title)[1]','nvarchar(max)') as Title,10     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID from Xml_Table11     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001';12 --修改數(shù)目編號(hào)為0001 的價(jià)格為 1113 update Xml_Table14     set XmlData.modify('replace value of (/book[@id="0001"]/price/text())[1] with "11"');15 --修改 所有的數(shù)目作者為Fly_1230016 update Xml_Table17     set XmlData.modify('replace value of (/book/author/text())[1] with "Fly_12300"')18 --查看是否編號(hào)為0001的價(jià)格修改為11,且所有作者修改為Fly_1230019 select  XmlData.value('(/book/price)[1]','nvarchar(max)') as Title,20     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,21     XmlData.value('(/book/author)[1]','nvarchar(max)') as Author from Xml_Table22     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001';23 --添加屬性  24 update Xml_Table25 set XmlData.modify('insert attribute isbn {"12300321"} into (/book)[1]');26 --查看是否存在屬性isbn27 select  XmlData.value('(/book/@isbn)[1]','nvarchar(max)') as isbn,28     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID from Xml_Table29     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001';30 --在編號(hào)為0001的添加子節(jié)點(diǎn) category  為 Computer 的分類31 update Xml_Table32     set XmlData.modify('insert <category>Computer</category> before (/book[@id=0001]/author)[1]');33 --查看是否添加了category節(jié)點(diǎn)34 select  XmlData.value('(/book/category)[1]','nvarchar(max)') as category,35     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,XmlData from Xml_Table36     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001';37 --刪除節(jié)點(diǎn)38 update Xml_Table39     set XmlData.modify('delete /book[@id=0001]/category');40 --查看是否刪除了category節(jié)點(diǎn)41 select  XmlData.value('(/book/category)[1]','nvarchar(max)') as category,42     XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,XmlData from Xml_Table43     where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001';44 --nodes()  查詢 book的編碼45 select ids.value('@id', 'varchar(max)'),ids.value('(title)[1]','nvarchar(max)') title from  Xml_Table 46     CROSS APPLY XmlData.nodes('//book') as X(ids) ;47 --exist()48 select XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID49     from Xml_Table 50     where XmlData.exist('(/book/@id)')=1 --判斷是否存在

如圖:

四、xml xpath

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

如圖：

五、跨域操作

 1 --根據(jù)Books 表中的ID，Xml_Table 表中的XmlData ID屬性  修改對應(yīng)的 title屬性 2 --即：根據(jù)在books中編碼0001的 的名稱 MSSQLServer2005 3 --修改為Xml_Table表中book編碼為0001的title為 MSSQLServer2005 4  5 declare @data xml 6 declare @id nvarchar(36) 7 declare @name nvarchar(64) 8 declare custore_name cursor for  9 select Books.ID,Xml_Table.XmlData,Books.Name10 from Books,Xml_Table11     where Books.ID= Xml_Table.XmlData.value('(/book/@id)[1]','nvarchar(max)');12  OPEN custore_name   13 FETCH NEXT FROM custore_name into @id, @data, @name14 WHILE(@@FETCH_STATUS=0)      15  BEGIN      16   set @data.modify(('replace value of (/book/title/text())[1] with sql:variable("@name")'))17   update Xml_Table set XmlData = @data where XmlData.value('(/book/@id)[1]','nvarchar(max)') = @id   18  FETCH NEXT FROM custore_name into   19     @id, @data, @name20  END    21  CLOSE custore_name  22  deallocate custore_name23 24  select * from Xml_Table

如圖所示：