摘要:本文主要向大家介绍了SQLServer数据库之SQL SERVER中对XML进行操作,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了SQLServer数据库之SQL SERVER中对XML进行操作,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
一、前言
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=‘
闲话少说,直接上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
10
11
12 ‘);
13 insert into Xml_Table(XmlData) values
14 (‘
15
16
17
18
19 ‘);
20 insert into Xml_Table(XmlData) values
21 (‘
22
23
24
25
26 ‘);
27 --3、查询
28 select * from Xml_Table;
结果如图:
三、对xml操作
对xml操作,也不做过多解析,如有不清晰的可以联系我;Emil:feifei12300@126.com
需要注意的是给每个节点添加属性或者添加节点的时候如果已经存在的会报错,所以最好是先exist(‘你的条件‘)=0 一下;
1 --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
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
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 --根据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
如图所示:
六、结束语
需要注意点:添加、修改属性或者节点需要先判断是否存在(exist);跨域操作时使用了游标,不熟悉的可以自己查阅相关资料。
本文由职坐标整理并发布,希望对同学们学习SQL Server有所帮助,更多内容请关注职坐标数据库SQL Server数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号