浅析sql server对xml简单操作教程

作者：袖梨 2022-06-29

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='

C Program

David

21

' 这样的，但是这仅仅是学习，不能真正用在项目或实际中缺乏实践性。因为很少有直接操作sql内存中的这些。

闲话少说，直接上SQL创建表语句

代码如下

复制代码

--1、创建xml测试数据库表Xml_Table Author:Fly , Email:[email protected]
use Fly_Test --测试数据库
go
create table Xml_Table(ID INT identity PRIMARY KEY, XmlData XML);
--2、插入测试数据
insert into Xml_Table(XmlData) values
('
SqlServer2005
Fly
21

');
insert into Xml_Table(XmlData) values
('
SqlServer2008
Fly
22

');
insert into Xml_Table(XmlData) values
('
SqlServer2012
Fly
23

');
--3、查询
select * from Xml_Table;

结果如图：

对xml操作

对xml操作，也不做过多解析，如有不清晰的可以联系我；Emil:[email protected]

需要注意的是给每个节点添加属性或者添加节点的时候如果已经存在的会报错,所以最好是先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 Computer 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

如图所示：