|
/*
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串拆分为行
/*
a
b
c
dd
ee
f
aa
a
aa
f
*/
--(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) 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,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,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]',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.73,?? 0.21,??? 0.12
insert @t select N'张五',??? 0.31,0.23,? 0.33,?? 0.91,??? 0.65
insert @t select N'张六',??? 0.59,0.11,? 0.26,? 0.13,??? 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,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>',''),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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
