T-Sql操作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字段不能用来作为主键或者索引键。同样，我们也可以使用SQL语句来创建使用XML字段的数据表，下面的语句创建一个名为“docs”的表，该表带有整型主键“pk”和非类型化的 XML 列“xCol”：
CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)
XML类型除了在表中使用，还可以在存储过程、事务、函数等中出现。下面我们来完成我们对XML操作的第一步，使用SQL语句定义一个XML类型的数据，并为它赋值：
declare @xmlDoc xml;
set @xmlDoc='<book id="0001">
<title>C Program</title>
<author>David</author>
<price>21</price>
</book>'
三、查询操作
在定义了一个XML类型的数据之后，我们最常用的就是查询操作，下面我们来介绍如何使用SQL语句来进行查询操作的。
在T-Sql中，提供了两个对XML类型数据进行查询的函数，分别是query(xquery)和value(xquery, dataType)，其中，query(xquery)得到的是带有标签的数据，而value(xquery, dataType)得到的则是标签的内容。接下类我们分别使用这两个函数来进行查询。
1、使用query(xquery) 查询
我们需要得到书的标题（title），使用query(xquery)来进行查询，查询语句为：
select @xmlDoc.query('/book/title')
运行结果如图：
![[转载]SQL对Xml字段的操作](https://i-blog.csdnimg.cn/blog_migrate/0b150b7c9258594befe2c3d78c7c238a.jpeg "[转载]SQL对Xml字段的操作")
2、使用value(xquery, dataType) 查询
同样是得到书的标题，使用value函数，需要指明两个参数，一个为xquery， 另一个为得到数据的类型。看下面的查询语句：
select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')
运行结果如图：
![[转载]SQL对Xml字段的操作](https://i-blog.csdnimg.cn/blog_migrate/695b91c02d04f9828c88f0414d8d3381.jpeg "[转载]SQL对Xml字段的操作")
3、查询属性值
无论是使用query还是value，都可以很容易的得到一个节点的某个属性值，例如，我们很希望得到book节点的id，我们这里使用value方法进行查询，语句为：
select @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')
运行结果如图：
![[转载]SQL对Xml字段的操作](https://i-blog.csdnimg.cn/blog_migrate/b496fdfb32f241dad126da05c6e95013.jpeg "[转载]SQL对Xml字段的操作")
4、使用xpath进行查询
xpath是.net平台下支持的，统一的Xml查询语句。使用XPath可以方便的得到想要的节点，而不用使用where语句。例如，我们在@xmlDoc中添加了另外一个节点，重新定义如下：
set @xmlDoc='<root>
<book id="0001">
<title>C# Program</title>
<author>Jerry</author>
<price>50</price>
</book>
<book id="0002">
<title>Java Program</title>
<author>Tom</author>
<price>49</price>
</book>
</root>'
--得到id为0002的book节点
select @xmlDoc.query('(/root/book[@id="0002"])')
上面的语句可以独立运行，它得到的是id为0002的节点。运行结果如下图：
![clip_image001[6]](http://images.cnblogs.com/cnblogs_com/youring2/WindowsLiveWriter/SQLXml_12290/clip_image001[6].jpg)
四、修改操作
SQL的修改操作包括更新和删除。SQL提供了modify()方法，实现对Xml的修改操作。modify方法的参数为XML修改语言。XML修改语言类似于SQL 的Insert、Delete、UpDate，但并不一样。
1、修改节点值
我们希望将id为0001的书的价钱(price)修改为100， 我们就可以使用modify方法。代码如下：
set @xmlDoc.modify('replace value of (/root/book[@id=0001]/price/text())[1] with "100"')
--得到id为0001的book节点
select @xmlDoc.query('(/root/book[@id="0001"])')
注意：modify方法必须出现在set的后面。运行结果如图：
2、删除节点
接下来我们来删除id为0002的节点，代码如下：
--删除节点id为0002的book节点
set @xmlDoc.modify('delete /root/book[@id=0002]')
select @xmlDoc
运行结果如图：
3、添加节点
很多时候，我们还需要向xml里面添加节点，这个时候我们一样需要使用modify方法。下面我们就向id为0001的book节点中添加一个ISBN节点，代码如下：
--添加节点
set @xmlDoc.modify('insert 78-596-134 before (/root/book[@id=0001]/price)[1]')
select @xmlDoc.query('(/root/book[@id="0001"]/isbn)')
运行结果如图：
4、添加和删除属性
当你学会对节点的操作以后，你会发现，很多时候，我们需要对节点进行操作。这个时候我们依然使用modify方法，例如，向id为0001的book节点中添加一个date属性，用来存储出版时间。代码如下：
--添加属性
set @xmlDoc.modify('insert attribute date{"2008-11-27"} into (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
运行结果如图：
如果你想同时向一个节点添加多个属性，你可以使用一个属性的集合来实现，属性的集合可以写成：(attribute date{"2008-11-27"}, attribute year{"2008"})，你还可以添加更多。这里就不再举例了。
5、删除属性
删除一个属性，例如删除id为0001 的book节点的id属性，我们可以使用如下代码：
--删除属性
set @xmlDoc.modify('delete root/book[@id="0001"]/@id')
select @xmlDoc.query('(/root/book)[1]')
运行结果如图：
6、修改属性
修改属性值也是很常用的，例如把id为0001的book节点的id属性修改为0005，我们可以使用如下代码：
--修改属性
set @xmlDoc.modify('replace value of (root/book[@id="0001"]/@id)[1] with "0005"')
select @xmlDoc.query('(/root/book)[1]')
运行结果如图：
exist()方法，用来判断指定的节点是否存在，返回值为true或false； nodes()方法，用来把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行。
declare @data xml
set @data='
<bookstore>
<book category="COOKING">
</book>
<book category="CHILDREN">
</book>
<book category="WEB">
</book>
<book category="WEB">
</book>
</bookstore>
'
--测试语句，如果不理解语法请参考上面的xpath规则和xquery函数说明
--1、文档
select @data
--2、任意级别是否存在price节点
select @data.exist('//price')
--3、获取所有book节点
select @data.query('//book')
--4、获取所有包含lang属性的节点
select @data.query('//*[@lang]')
--5、获取第一个book节点
select @data.query('//book[1]')
--6、获取前两个book节点
select @data.query('//book[position()<=2]')
--7、获取最后一个book节点
select @data.query('//book[last()]')
--8、获取price>35的所有book节点
select @data.query('//book[price>35]')
--9、获取category="WEB"的所有book节点
select @data.query('//book[@category="WEB"]')
--10、获取title的lang="en"的所有book节点
select @data.query('//book/title[@lang="en"]')
--11、获取title的lang="en"且 price>35的所有book节点
select @data.query('//book[./title[@lang="en"] or price>35 ]')
--12、获取title的lang="en"且 price>35的第一book的(第一个)title
select @data.query('//book[./title[@lang="en"] and price>35 ]').value('(book/title)[1]','varchar(max)')
--13、等价于12
select @data.value('(//book[./title[@lang="en"] and price>35 ]/title)[1]','varchar(max)')
--14、获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性
select @data.value('((//book[@category="WEB" and price>35 ]/title)[1]/@lang)[1]','varchar(max)')
--15、获取第一本书的title
select Tab.Col.value('(book/title)[1]','varchar(max)') as title
--16、获取每本书的第一个author
select Tab.Col.value('author[1]','varchar(max)') as title
--17、获取所有book的所有信息
select
from @data.nodes('//book') as T(C)
--18、获取不是日语(lang!="jp")且价格大于35的书的所有信息
select
from @data.nodes('//book[./title[@lang!="jp"] and price>35 ]') as T(C)
扫一扫
910
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
