SQL操作XML

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')运行结果如图：2、使用value(xquery, dataType) 查询同样是得到书的标题，使用value函数，需要指明两个参数，一个为xquery，另一个为得到数据的类型。

看下面的查询语句：select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')运行结果如图：3、查询属性值无论是使用query还是value，都可以很容易的得到一个节点的某个属性值，例如，我们很希望得到book节点的id，我们这里使用value方法进行查询，语句为：select @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')运行结果如图：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的节点。

运行结果如下图：四、修改操作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 <isbn>78-596-134</isbn> 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]')运行结果如图：OK，经过上面的学习，相信你已经可以很好的在SQL中使用Xml类型了，下面是我们没有提到的，你可以去其它地方查阅：exist()方法，用来判断指定的节点是否存在，返回值为true或false；nodes()方法，用来把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行。

三前言:此文只讲xml数据类型及相应的一些操作方法在解决日常T－SQL编程中的一些应用，而避开xml modify,xml schema,xml索引,命名空间等这些语法性或者生硬的一些问题(这些语法您可以查联机丛书),即此文主要讲以xml的一些操作特性及xquery去解决编程问题.Tags:xquery ,FLWOR迭带,sql:column ,sql:variable ,nodes ,value ,query ,xpath ,xquery function, if, 聚合函数, xs:function等典型应用举例:--(1)--====================================================================--拆分DECLARE@s VARCHAR(100)SET@s='a,b,c,dd,ee,f,aa,a,aa,f'--常规做法(sql2000常用),以一split函数拆分串为表类型结构,如--SELECT * FROM dbo.split(@s,',') a--当然，也可能是循环去拆分,或者以一输助表的identity列利用charindex等函数拿identity列值与','的位置匹配实现拆分--这些做法，roy_88及本人以前都整理过，不再累赘，可见推荐贴。

即便是xml法，也贴过多次，下面一笔带过--XML做法:SELECT b.v FROM(SELECT CAST('<r>'+REPLACE(@s,',','</r><r>') +'</r>'AS XML) x) a --将字串","换换为"</r><r>"并前后拼上<r>,</r>以用来构造xml串CROSS APPLY(SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b --使用 xml.nodes函数将xml串拆分为行/*abcddeefaaaaf*/--(2)--====================================================================--去重,@s中出现的元素，重复的只要一个,希望结果为 'a,b,c,dd,ee,f'--常规做法，循环或函数,或临时表拆后distinct--XML做法:--a.在(1)的基础上进行;WITH fc AS--定义cte命名,将@s转换为一个表结构(SELECT DISTINCT b.v vFROM(SELECT CAST('<r>'+REPLACE(@s,',','</r><r>') +'</r>'AS X ML) x) aCROSS APPLY(SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b--对这个表利用xml方法进行行值拼接SELECT STUFF(b.v.value('/r[1]','varchar(100)'),1,1,'')FROM(SELECT v=(SELECT','+ v FROM fc FOR XML PATH(''),ROOT('r'),TYPE)) b/*a,aa,b,c,dd,ee,f*/--b FLWOR语句 + T-SQL组合:SELECT STUFF(v,1,1,'') FROM(SELECT CAST('<r>'+REPLACE(@s,',','</r><r>') +'</r>'AS XML) x) a CROSS APPLY(SELECT x=(SELECT t.x.value('.','varchar(10)') v,idx=ROW_NUMBER() OVER(O RDER BY GETDATE()) FROM a.x.nodes('//r') AS t(x) FOR XML PATH('r'),TYPE)) b --利用row_number得到唯一idxCROSS APPLY(SELECT v=CAST(b.x.query('for $r in //r where count(//r[v=$r/v and idx<$ r/idx])=0 return concat(",",xs:string($r/v[1]))') AS VARCHAR(MAX))) c --类似count计数法，取得v相同的节点集idx值最小的节点,原型为:--SELECT * FROM tb a WHERE 1>(SELECT COUNT(*) FROM tb WHERE v=a.v AND id<a.i d)/*a ,b ,c ,dd ,ee ,aa ,f*/--c distinct-valuesSELECT REPLACE(v,' ',',') FROM(SELECT CAST('<r>'+REPLACE(@s,',','</r><r>') +'</r>'AS XML) x) a CROSS APPLY(SELECT CAST(a.x.query('distinct-values(//r)') AS VARCHAR(MAX)) v) b --直接调用distinct-values函数来操作/*a,b,c,dd,ee,f,aa*/-- 导入去重, last() , position()DECLARE@doc xmlSET@doc='<?xml version="1.0" encoding="gb2312" ?><employees><employee><empid>e0001</empid><name>萧峰</name></employee><employee><empid>e0002</empid><name>段誉</name></employee><employee><empid>e0003</empid><name>王语嫣</name></employee><employee><empid>e0003</empid><name>张无忌</name></employee></employees>'create table people2(personid varchar(10) primary key , name varchar(20))INSERT people2SELECT DISTINCT b.*FROM(SELECT x =@doc.query('for $e in //employee return //employee[empid = $e/empid][last()]')) a --FLWOR时,用当前节点去//emploee节点集中找节点集中empid等于当前节点的empid, 在找到的集合中取最后一个利用last()函数CROSS APPLY(SELECT id=t.x.value('empid[1]','varchar(100)'),name=t.x.value('name[1]' ,'varchar(100)') FROM a.x.nodes('//employee') AS t(x)) bSELECT*FROM people2/*e0001 萧峰e0002 段誉e0003 张无忌*/GOdrop table people2GO--同组一选多，也可应用此方法，不过没有必要，就不再累赘了。