一、前言
SQL Server 2005 引入了一种称为 XML 的本机数据类型。用户可以创建这样的表，它在关系列之外还有一个或多个 XML 类型的列；此外，还允许带有变量和参数。为了更好地支持 XML 模型特征（例如文档顺序和递归结构），XML 值以内部格式存储为大型二进制对象 (BLOB)。
用户将一个XML数据存入数据库的时候，可以使用这个XML的字符串，SQL Server会自动的将这个字符串转化为XML类型，并存储到数据库中。
随着SQL Server 对XML字段的支持，相应的，T-SQL语句也提供了大量对XML操作的功能来配合SQL Server中XML字段的使用。本文主要说明如何使用SQL语句对XML进行操作。（以上摘自Qi Fei's Blog）
首先要明确一个基本原则，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 --1、创建xml测试数据库表Xml_Table Author:Fly , Email:feifei12300@126.com 2 use Fly_Test --测试数据库 3 go 4 create table Xml_Table(ID INT identity PRIMARY KEY, XmlData XML); 5 --2、插入测试数据 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) values 14 ('<book id="0002"> 15 <title>SqlServer2008</title> 16 <author>Fly</author> 17 <price>22</price> 18 </book> 19 '); 20 insert into Xml_Table(XmlData) values 21 ('<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;
三、对xml操作
对xml操作，也不做过多解析，如有不清晰的可以联系我；Emil:feifei12300@126.com
需要注意的是给每个节点添加属性或者添加节点的时候如果已经存在的会报错,所以最好是先exist('你的条件')=0 一下；
--4、对XML操作真正开始了 2 --SQLServer2005 中对 XML 的处理功能显然增强了很多，提供了 query(),value(),exist(),modify(),nodes() 3 --查询所有书的名称及作者 4 select XmlData.query('/book') as Title,XmlData.query('/book/author') as Author from Xml_Table; 5 --显然这不是我们想要的数据 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 --查询数目编号为0001的书的信息 9 select XmlData.value('(/book/title)[1]','nvarchar(max)') as Title, 10 XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID from Xml_Table 11 where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; 12 --修改数目编号为0001 的价格为 11 13 update Xml_Table 14 set XmlData.modify('replace value of (/book[@id="0001"]/price/text())[1] with "11"'); 15 --修改 所有的数目作者为Fly_12300 16 update Xml_Table 17 set XmlData.modify('replace value of (/book/author/text())[1] with "Fly_12300"') 18 --查看是否编号为0001的价格修改为11,且所有作者修改为Fly_12300 19 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_Table 22 where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; 23 --添加属性 24 update Xml_Table 25 set XmlData.modify('insert attribute isbn {"12300321"} into (/book)[1]'); 26 --查看是否存在属性isbn 27 select XmlData.value('(/book/@isbn)[1]','nvarchar(max)') as isbn, 28 XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID from Xml_Table 29 where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; 30 --在编号为0001的添加子节点 category 为 Computer 的分类 31 update Xml_Table 32 set XmlData.modify('insert <category>Computer</category> before (/book[@id=0001]/author)[1]'); 33 --查看是否添加了category节点 34 select XmlData.value('(/book/category)[1]','nvarchar(max)') as category, 35 XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,XmlData from Xml_Table 36 where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001'; 37 --删除节点 38 update Xml_Table 39 set XmlData.modify('delete /book[@id=0001]/category'); 40 --查看是否删除了category节点 41 select XmlData.value('(/book/category)[1]','nvarchar(max)') as category, 42 XmlData.value('(/book/@id)[1]','nvarchar(max)') as BookID,XmlData from Xml_Table 43 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 BookID 49 from Xml_Table 50 where XmlData.exist('(/book/@id)')=1 --判断是否存在
四、xml xpath
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,'');
五、跨域操作
--根据Books 表中的ID，Xml_Table 表中的XmlData ID属性 修改对应的 title属性 2 --即：根据在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.Name 10 from Books,Xml_Table 11 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, @name 14 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, @name 20 END 21 CLOSE custore_name 22 deallocate custore_name 23 24 select * from Xml_Table 复制代码
六：多表关联批量更新
1 create table #Friend 2 ( 3 ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 4 Friend XML 5 ) 6 7 INSERT INTO #Friend SELECT '<Friends> 8 <friend name="junwenli" sex="man" age="23"></friend> 9 <friend name="jinhanliu" sex="man" age="24"></friend> 10 <friend name="fangcheng" sex="man" age="23"></friend> 11 </Friends>' 12 13 create table #Temp 14 ( 15 ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 16 FriendName NVARCHAR(32) 17 ) 18 INSERT INTO #Temp SELECT 'GuoHu'; 19 20 select * from #Friend 21 select * from #Temp 22 23 UPDATE F 24 SET Friend.modify('replace value of (/Friends/friend/@name)[1] with sql:column("T.FriendName")') 25 FROM #Friend F,#Temp T 26 WHERE F.ID = T.ID; 27
1 七：通过SQL统计XML 中某一个节点的个数 2 3 4 5 <Request> 6 <Head> 7 <Region>APAC</Region> 8 <Country>CN</Country> 9 <Env>UAT</Env> 10 </Head> 11 <Segment> 12 <Prod>MasterCard</Prod> 13 </Segment> 14 <Segment> 15 <Prod>MasterCard</Prod> 16 </Segment> 17 <Segment> 18 <Prod>MasterCard</Prod> 19 </Segment> 20 </Request> 21 22 表table_response中的列response的值为XML 格式，值为上面的数据 23 24 统计Prod的节点的个数 25 26 select response.value('count(/Request/Segment/Prod)','int') as count ,req_id from table_response
结果是3个Prod 节点。