/*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,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串拆分为行/*abcddeefaaaaaf*/--(2)--====================================================================--去重,@s中出现的元素，重复的只要一个,希望结果为 'a,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,'') FROM (SELECT v=(SELECT ',' + v FROM fc FOR XML PATH(''),ROOT('r'),TYPE)) b/*a,f*/--b FLWOR语句 + T-sql组合:SELECT STUFF(v,'') FROM (SELECT CAST('<r>' + REPLACE(@s,'</r><r>') + '</r>' AS XML) x) aCROSS 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得到唯一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.ID)/*a,f*/--c distinct-valuesSELECT REPLACE(v,' ',') FROM (SELECT CAST('<r>' + REPLACE(@s,'</r><r>') + '</r>' AS XML) x) aCROSS APPLY (SELECT CAST(a.x.query('distinct-values(//r)') AS VARCHAR(MAX)) v) b --直接调用distinct-values函数来 *** 作/*a,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]',name=t.x.value('name[1]','varchar(100)') FROM a.x.nodes('//employee') AS t(x)) bSELECT * FROM people2/*e0001 萧峰e0002 段誉e0003 张无忌*/GOdrop table people2 GO--同组一选多，也可应用此方法，不过没有必要，就不再累赘了。--(3)--====================================================================--列名,列值相关--a,按行聚合declare @t table(Sname nvarchar(5),V1 float,V2 float,V3 float,V4 float,V5 float,V6 float) insert @t select N'张三',0.11,0.21,0.29,0.32,0.08 insert @t select N'李四',0.01,0.61,0.73,0.12 insert @t select N'张五',0.31,0.23,0.33,0.91,0.65 insert @t select N'张六',0.59,0.26,0.13,0.15 select b.* from (select x=cast((select * from @t for xml path('r')) as xml)) across apply ( select name=x.query('./Sname/text()'),v=x.query('max(./*[local-name(.)!="Sname"])') from a.x.nodes('//r') as t(x) --r为二级节点(因为文档本身无根节点，即为每项的顶级节点)即为一个r节点表示一条记录. r下级节点，每个表示一个列，因为列名未知，所以用/*匹配所有节点，因为name为区别列，不参与聚合运算，故用local-name取得来过滤 ) b/*张三 0.32李四 0.73张五 0.91张六 0.59*/--b,由值引到取列if not object_ID('T1') is null drop table T1GOCreate table T1([tID] int,[tname] nvarchar(4))Insert T1select 1,N'zhao' union allselect 2,N'qian' union allselect 3,N'sun'Go--> --> 借且(Roy)生成測試數據 if not object_ID('T2') is null drop table T2GoCreate table T2([tID] int,[zhao] nvarchar(1),[qian] nvarchar(1),[sun] nvarchar(1))Insert T2select 1,N'a',N'b',N'c' union allselect 2,N'd',N'e',N'f' union allselect 3,N'g',N'h',N'i'GoSELECT c.tID,c.tname,v FROM t1 cCROSS APPLY (SELECT x=(SELECT * FROM t2 WHERE tID=c.tID FOR XML PATH('r'),TYPE)) aCROSS APPLY (SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("c.tname")) ]/text()') FROM a.x.nodes('//r') AS t(x) ) b/*1 zhao a2 qian e3 sun i*/--c,列名，列值，与系统表 CREATE table tb(f1 INT,f2 INT,x INT,z INT,d INT,ex INT,dd INT,vv INT) INSERT tb SELECT 1,2,3,5,11,2423,33 GO SELECT * FROM tb GO SELECT name,v FROM ( SELECT name FROM sys.columns WHERE object_ID=object_ID('tb','u') ) a CROSS JOIN (SELECT x=(SELECT * FROM tb FOR XML PATH('r'),TYPE)) b CROSS APPLY (SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("a.name")) ]/text()') FROM b.x.nodes('//r') AS t(x) ) c /*f1 1f2 2x 3z 5d 11ex 3dd 2423vv 33*/GO DROP table tbGO--(4)--一些综合计算--以下表 ta.a值 yyyymmdd-yyyymmdd表连续时间段，","表单个日期If object_ID('ta','u') is not null Drop table taGoCreate table ta(a varchar(100))GoInsert into taselect '1 ¦ ¦20080101-20080911' union allselect '2 ¦ ¦20080101，20080201，20080301，20080515，20080808'union allselect '3 ¦ ¦20080101，20080201，20080301，20080515，20081108'Godeclare @s varchar(8)select @s= convert(varchar(8),getdate(),112)select stuff(replace(replace(cast(x as varchar(1000)),'</item><item>',case when type='1' then '-' else '，' end),'</item>',''),6,type + ' ¦ ¦') a from ( select left(a,1) type,cast( '<item>' + replace( stuff(a,case when left(a,1)=1 then '-' else '，' end,'</item><item>' ) + '</item>' AS XML ) x from ta ) base where x.value(' if (sql:column("base.type")="1") then if( (/item/text())[1]<sql:variable("@s") and (/item/text())[2]>sql:variable("@s") ) then 1 else 0 else count(//item[text()>sql:variable("@s")]) ','int' )>0go待续。。 总结 以上是内存溢出为你收集整理的SQLServer2005 XML在T－SQL查询中的典型应用全部内容，希望文章能够帮你解决SQLServer2005 XML在T－SQL查询中的典型应用所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）