Sql Server XML 操作实例

/* 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 张无忌 */ GO DROP 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.11 , 0.08 INSERT @t SELECT N'李四' , 0.01 , 0.61 , 0.21 , 0.73 , 0.21 , 0.12 INSERT @t SELECT N'张五' , 0.31 , 0.21 , 0.23 , 0.33 , 0.91 , 0.65 INSERT @t SELECT N'张六' , 0.59 , 0.11 , 0.26 , 0.13 , 0.01 , 0.15 SELECT b.* FROM ( SELECT x = CAST(( SELECT * FROM @t FOR XML PATH('r') ) AS XML) ) a CROSS 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 T1 GO CREATE TABLE T1 ( [tId] INT , [tName] NVARCHAR(4) ) INSERT T1 SELECT 1 , N'zhao' UNION ALL SELECT 2 , N'qian' UNION ALL SELECT 3 , N'sun' Go --> --> 借且(Roy)生成測試數據 IF NOT OBJECT_ID('T2') IS NULL DROP TABLE T2 Go CREATE TABLE T2 ( [tId] INT , [zhao] NVARCHAR(1) , [qian] NVARCHAR(1) , [sun] NVARCHAR(1) ) INSERT T2 SELECT 1 , N'a' , N'b' , N'c' UNION ALL SELECT 2 , N'd' , N'e' , N'f' UNION ALL SELECT 3 , N'g' , N'h' , N'i' Go SELECT c.tid , c.tName , v FROM t1 c CROSS APPLY ( SELECT x = ( SELECT * FROM t2 WHERE tid = c.tid FOR XML PATH('r') , TYPE ) ) a CROSS 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 a 2 qian e 3 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 , 3 , 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 1 f2 2 x 3 z 5 d 11 ex 3 dd 2423 vv 33 */ GO DROP TABLE tb GO --(4) --一些综合计算 --以下表 ta.a值 yyyymmdd-yyyymmdd表连续时间段，","表单个日期 IF OBJECT_ID('ta', 'u') IS NOT NULL DROP TABLE ta Go CREATE TABLE ta ( a VARCHAR(100) ) Go INSERT INTO ta SELECT '1 | |20080101-20080911' UNION ALL SELECT '2 | |20080101，20080201，20080301，20080515，20080808' UNION ALL SELECT '3 | |20080101，20080201，20080301，20080515，20081108' Go DECLARE @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>', ''), 1, 6, type + ' | |') a FROM ( SELECT LEFT(a, 1) type , CAST('<item>' + REPLACE(STUFF(a, 1, 5, ''), 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') > 0 go