首页 > 代码库 > SQL SERVER中对XML进行操作
SQL SERVER中对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进行操作。（以上摘自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) values14 (‘<book id="0002">15 <title>SqlServer2008</title>16 <author>Fly</author>17 <price>22</price>18 </book>19 ‘);20 insert into Xml_Table(XmlData) values21 (‘<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 一下；
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_Table11 where XmlData.value(‘(/book/@id)[1]‘,‘nvarchar(max)‘) = ‘0001‘;12 --修改数目编号为0001 的价格为 1113 update Xml_Table14 set XmlData.modify(‘replace value of (/book[@id="0001"]/price/text())[1] with "11"‘);15 --修改 所有的数目作者为Fly_1230016 update Xml_Table17 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_Table25 set XmlData.modify(‘insert attribute isbn {"12300321"} into (/book)[1]‘);26 --查看是否存在属性isbn27 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_Table32 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_Table36 where XmlData.value(‘(/book/@id)[1]‘,‘nvarchar(max)‘) = ‘0001‘;