日期:2014-05-18 浏览次数:21029 次
/* SQLServer2005 XML在T-SQL查询中的典型应用 整理:fcuandy 时间:2008.11.7 前言: 此文只讲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串拆分为行 /* a b c dd ee f aa a aa f */ --(2) --==================================================================== --去重,@s中出现的元素,重复的只要一个,希望结果为 'a,b,c,dd,ee,f' --常规做法,循环或函数,或临时表拆后distinct --XML做法: --a.在(1)的基础上进行 ;WITH fc AS --定义cte命名,将@s转换为一个表结构 ( SELECT DISTINCT b.v v FROM (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a CROSS 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(ORDER BY GETDATE()) FROM a.x.nodes('//r') AS t(x) FOR XML PATH('r'),TYPE)) b --利用row_number得到唯一idx CROSS 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.id) /* a ,b ,c ,dd ,ee ,aa ,f */ --c distinct-values SELECT 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 xml SET @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 people2 SELECT 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)) b SELECT * FROM people2 /* e0001 萧峰 e0002 段誉 e0003 张无忌 */ G