sqlserver中对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='David21

' 这样的，但是这仅仅是学习，不能真正⽤在项⽬或实际中缺乏实践性。因为很少有直接操作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 ('

8 9 Fly10 2111 12 ');

13 insert into Xml_Table(XmlData) values14 ('

15 16 Fly17 2218 19 ');

20 insert into Xml_Table(XmlData) values21 ('

22 23 Fly24 2325 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_Table11 where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001';12 --修改数⽬编号为0001 的价格为 1113 update Xml_Table

14 set XmlData.modify('replace value of (/book[@id=\"0001\"]/price/text())[1] with \"11\"');15 --修改 所有的数⽬作者为Fly_1230016 update Xml_Table

17 set XmlData.modify('replace value of (/book/author/text())[1] with \"Fly_12300\"')18 --查看是否编号为0001的价格修改为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_Table

25 set XmlData.modify('insert attribute isbn {\"12300321\);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_Table29 where XmlData.value('(/book/@id)[1]','nvarchar(max)') = '0001';30 --在编号为0001的添加⼦节点 category 为 Computer 的分类31 update Xml_Table

32 set XmlData.modify('insert Computer 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_Table36 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_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

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.Name10 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, @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复制代码

六：多表关联批量更新

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 '

8 9 10 11 '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 #Friend21 select * from #Temp22

23 UPDATE F

24 SET Friend.modify('replace value of (/Friends/friend/@name)[1] with sql:column(\"T.FriendName\")')25 FROM #Friend F,#Temp T26 WHERE F.ID = T.ID;27

1 七：通过SQL统计XML 中某⼀个节点的个数 2 3 4

5 6

7 APAC 8 CN 9 UAT10 11

12 MasterCard 13 14

15 MasterCard 16 17

18 MasterCard 19 20 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 节点。