SQLServer2005 XML在T－SQL查询中的典型应用

1. /*
2. SQLServer2005 XML在T－SQL查询中的典型应用
4. 整理:fcuandy
5. 时间:2008.11.7
7. 前言:
8.     此文只讲xml数据类型及相应的一些操作方法在解决日常T－SQL编程中的一些应用，而避开xml modify,
9. xml schema,xml索引,命名空间等这些语法性或者生硬的一些问题(这些语法您可以查联机丛书),即此文主要
10. 讲以xml的一些操作特性及xquery去解决编程问题.
12. Tags:
13.     xquery ,FLWOR迭带 ,sql:column ,sql:variable ,nodes ,value ,query ,xpath ,xquery function, if, 聚合函数, xs:function等
15. 典型应用举例:
16. */
19. --(1)
20. --====================================================================
21. --拆分
22. DECLARE @s VARCHAR(100)
23. SET @s='a,b,c,dd,ee,f,aa,a,aa,f'
25. --常规做法(sql2000常用),以一split函数拆分串为表类型结构,如
26. --SELECT * FROM dbo.split(@s,',') a
27. --当然，也可能是循环去拆分,或者以一输助表的identity列利用charindex等函数拿identity列值与','的位置匹配实现拆分
28. --这些做法，roy_88及本人以前都整理过，不再累赘，可见推荐贴。即便 是xml法，也贴过多次，下面一笔带过
30. --XML做法:
31. SELECT b.v FROM
32.     (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a   --将字串","换换为"</r><r>"并前后拼上<r>,</r>以用来构造xml串
33. CROSS APPLY
34.     (SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b  --使用 xml.nodes函数将xml串拆分为行
35. /*
36. a
37. b
38. c
39. dd
40. ee
41. f
42. aa
43. a
44. aa
45. f
46. */
49. --(2)
50. --====================================================================
51. --去重,@s中出现的元素，重复的只要一个,希望结果为 'a,b,c,dd,ee,f'
52. --常规做法，循环或函数,或临时表拆后distinct
53. --XML做法:
54. --a.在(1)的基础上进行
56. ;WITH fc AS   --定义cte命名,将@s转换为一个表结构
57. (
58.     SELECT DISTINCT b.v v
59.             FROM
60.                 (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
61.             CROSS APPLY
62.                 (SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b
63. )
64. --对这个表利用xml方法进行行值拼接
65. SELECT STUFF(b.v.value('/r[1]','varchar(100)'),1,1,'')
66.     FROM
67.     (SELECT v=(SELECT ',' + v FROM fc FOR XML PATH(''),ROOT('r'),TYPE)) b
68. /*
69. a,aa,b,c,dd,ee,f
70. */
72. --b FLWOR语句 + T-SQL组合:
73. SELECT STUFF(v,1,1,'') FROM
74.     (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
75. CROSS APPLY
76.     (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
77. CROSS APPLY
78.     (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值最小的节点,原型为:
79. --SELECT * FROM tb a WHERE 1>(SELECT COUNT(*) FROM tb WHERE v=a.v AND id<a.id)
80. /*
81. a ,b ,c ,dd ,ee ,aa ,f
82. */
85. --c distinct-values
86. SELECT REPLACE(v,' ',',') FROM
87.     (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
88. CROSS APPLY
89.     (SELECT CAST(a.x.query('distinct-values(//r)') AS VARCHAR(MAX)) v) b  --直接调用distinct-values函数来操作
90. /*
91. a,b,c,dd,ee,f,aa
92. */
95. -- 导入去重, last() , position()
97. DECLARE   @doc  xml
98. SET   @doc   ='<?xml version="1.0" encoding="gb2312" ?>
99. <employees>
100.     <employee>
101.         <empid>e0001</empid>
102.         <name>萧峰</name>
103.     </employee>
104.     <employee>
105.         <empid>e0002</empid>
106.         <name>段誉</name>
107.     </employee>
108.     <employee>
109.         <empid>e0003</empid>
110.         <name>王语嫣</name>
111.     </employee>
112.     <employee>
113.         <empid>e0003</empid>
114.         <name>张无忌</name>
115.     </employee>
116. </employees>
117. '
118. create table people2 
119. ( 
120.     personid varchar(10)  primary key ,
121.     name varchar(20) 
122.  )
124. INSERT people2
125. SELECT DISTINCT b.* FROM
126.     (SELECT x = @doc.query('for $e in //employee  return  //employee[empid = $e/empid][last()]')) a  --FLWOR时,用当前节点去//emploee节点集中找节点集中empid等于当前节点的empid, 在找到的集合中取最后一个利用last()函数
127. CROSS APPLY
128.     (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
130. SELECT * FROM people2
131. /*
132. e0001    萧峰
133. e0002    段誉
134. e0003    张无忌
135. */
136. GO
137. drop table people2 
138. GO
139. --同组一选多，也可应用此方法，不过没有必要，就不再累赘了。
142. --(3)
143. --====================================================================
144. --列名,列值相关
145. --a,按行聚合
146. declare @t table(Sname nvarchar(5),  V1 float,    V2 float,    V3 float,      V4 float,    V5 float,      V6 float) 
147. insert @t select N'张三',    0.11 , 0.21 , 0.29,  0.32 ,   0.11,    0.08 
148. insert @t select N'李四',    0.01 , 0.61 , 0.21,  0.73 ,   0.21,    0.12 
149. insert @t select N'张五',    0.31 , 0.21 , 0.23,  0.33 ,   0.91,    0.65 
150. insert @t select N'张六',    0.59 , 0.11,  0.26,  0.13,    0.01,    0.15 
152. select b.* from
153.     (select x=cast((select * from @t for xml path('r')) as xml)) a
154. cross apply
155.     (
156.         select name=x.query('./Sname/text()'),v=x.query('max(./*[local-name(.)!="Sname"])') from a.x.nodes('//r') as t(x)  
157.         --r为二级节点(因为文档本身无根节点，即为每项的顶级节点)即为一个r节点表示一条记录. r下级节点，每个表示一个列，因为列名未知，所以用/*匹配所有节点，因为name为区别列，不参与聚合运算，故用local-name取得来过滤
158.     ) b
160. /*
161. 张三    0.32
162. 李四    0.73
163. 张五    0.91
164. 张六    0.59
165. */
167. --b ,由值引到取列
168. if not object_id('T1') is null
169.     drop table T1
170. GO
171. Create table T1([tId] int,[tName] nvarchar(4))
172. Insert T1
173. select 1,N'zhao' union all
174. select 2,N'qian' union all
175. select 3,N'sun'
176. Go
177. --> --> 借且(Roy)生成測試數據
178.  
179. if not object_id('T2') is null
180.     drop table T2
181. Go
182. Create table T2([tId] int,[zhao] nvarchar(1),[qian] nvarchar(1),[sun] nvarchar(1))
183. Insert T2
184. select 1,N'a',N'b',N'c' union all
185. select 2,N'd',N'e',N'f' union all
186. select 3,N'g',N'h',N'i'
187. Go
190. SELECT c.tid,c.tName,v FROM t1 c
191. CROSS APPLY
192.     (SELECT x=(SELECT * FROM t2 WHERE tid=c.tid FOR XML PATH('r'),TYPE)) a
193. CROSS APPLY
194.     (SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("c.tName")) ]/text()') 
195.         FROM a.x.nodes('//r') AS t(x)
196.     ) b
198. /*
199. 1    zhao    a
200. 2    qian    e
201. 3    sun    i
202. */
205. --c, 列名，列值，与系统表 
207. CREATE TABLE tb(f1 INT,f2 INT,x INT,z INT,d INT,ex INT,dd INT,vv INT) 
208. INSERT tb SELECT 1,2,3,5,11,3,2423,33 
209. GO 
210. SELECT * FROM tb 
211. GO 
212. SELECT name,v FROM
213.   ( SELECT name FROM sys.columns WHERE object_idobject_id=object_id('tb','u') ) a 
214. CROSS JOIN
215.   (SELECT x=(SELECT * FROM tb FOR XML PATH('r'),TYPE)) b 
216. CROSS APPLY
217.  (SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("a.name")) ]/text()') FROM b.x.nodes('//r') AS t(x) ) c 
218. /*
219. f1  1
220. f2  2
221. x   3
222. z   5
223. d   11
224. ex  3
225. dd  2423
226. vv  33
227. */
228. GO 
229. DROP TABLE tb
230. GO
232. --(4)
233. --一些综合计算
234. --以下表 ta.a值 yyyymmdd-yyyymmdd表连续时间段，","表单个日期
235. If object_id('ta','u') is not null 
236.     Drop table ta
237. Go
238. Create table ta(a varchar(100))
239. Go
240. Insert into ta
241. select '1 ¦ ¦20080101-20080911' 
242. union all
243. select '2 ¦ ¦20080101，20080201，20080301，20080515，20080808'
244. union all
245. select '3 ¦ ¦20080101，20080201，20080301，20080515，20081108'
246. Go
248. declare @s varchar(8)
249. select @s= convert(varchar(8),getdate(),112)
251. select stuff(replace(replace(cast(x as varchar(1000)),'</item><item>',case when type='1' then '-' else '，' end),'</item>',''),1,6,type + ' ¦ ¦') a
252.     from
253.     (
254.         select left(a,1) type, 
255.             cast(
256.                     '<item>' 
257.                     + 
258.                     replace(
259.                         stuff(a,1,5,''),
260.                         case when left(a,1)=1 then '-' else '，' end,
261.                         '</item><item>'
262.                         )
263.                     + 
264.                     '</item>'
265.                 AS XML
266.                 ) x
267.         from ta
268.     ) base
270.     where x.value('
271.             if (sql:column("base.type")="1") then
272.                 if(
273.                     (/item/text())[1]<sql:variable("@s")
274.                     and
275.                     (/item/text())[2]>sql:variable("@s")
276.                 )
277.                 then 1
278.                 else 0
279.             else
280.                 count(//item[text()>sql:variable("@s")])
281.             '
282.             ,
283.             'int'
284.             )>0
285. go

待续..