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,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,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,'')
66. ????FROM
67. ????(SELECT?v=(SELECT?','?+?v?FROM?fc?FOR?XML?PATH(''),ROOT('r'),TYPE))?b
68. /*
69. a,f
70. */
72. --b?FLWOR语句?+?T-SQL组合:
73. SELECT?STUFF(v,'')?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,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]',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.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,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>',''),6,type?+?'?|?|')?a
252. ????from
253. ????(
254. ????????select?left(a,1)?type,?
255. ????????????cast(
256. ????????????????????'<item>'?
257. ????????????????????+?
258. ????????????????????replace(
259. ????????????????????????stuff(a,
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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!