| 以下为引用的内容：
源代码网整理以下*
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
|
