*
SQLServer2005 XML在T－SQL查詢中的典型應(yīng)用

整理:fcuandy
時間:2008.11.7

前言:
此文只講xml數(shù)據(jù)類型及相應(yīng)的一些操作方法在解決日常T－SQL編程中的一些應(yīng)用，而避開xml modify,
xml schema,xml索引,命名空間等這些語法性或者生硬的一些問題(這些語法您可以查聯(lián)機叢書),即此文主要
講以xml的一些操作特性及xquery去解決編程問題.

Tags:
xquery ,FLWOR迭帶 ,sql:column ,sql:variable ,nodes ,value ,query ,xpath ,xquery function, if, 聚合函數(shù), xs:function等

典型應(yīng)用舉例:
*/

--(1)
--====================================================================
--拆分
DECLARE @s VARCHAR(100)
SET @s='a,b,c,dd,ee,f,aa,a,aa,f'

--常規(guī)做法(sql2000常用),以一split函數(shù)拆分串為表類型結(jié)構(gòu),如
--SELECT * FROM dbo.split(@s,',') a
--當(dāng)然，也可能是循環(huán)去拆分,或者以一輸助表的identity列利用charindex等函數(shù)拿identity列值與','的位置匹配實現(xiàn)拆分
--這些做法，roy_88及本人以前都整理過，不再累贅，可見推薦貼。即便 是xml法，也貼過多次，下面一筆帶過

--XML做法:
SELECT b.v FROM
(SELECT CAST('<r>' REPLACE(@s,',','</r><r>') '</r>' AS XML) x) a --將字串","換換為"</r><r>"并前后拼上<r>,</r>以用來構(gòu)造xml串
CROSS APPLY
(SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b --使用 xml.nodes函數(shù)將xml串拆分為行
/*
a
b
c
dd
ee
f
aa
a
aa
f
*/

--(2)
--====================================================================
--去重,@s中出現(xiàn)的元素，重復(fù)的只要一個,希望結(jié)果為 'a,b,c,dd,ee,f'
--常規(guī)做法，循環(huán)或函數(shù),或臨時表拆后distinct
--XML做法:
--a.在(1)的基礎(chǔ)上進(jìn)行

;WITH fc AS --定義cte命名,將@s轉(zhuǎn)換為一個表結(jié)構(gòu)
(
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方法進(jìn)行行值拼接
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計數(shù)法，取得v相同的節(jié)點集idx值最小的節(jié)點,原型為:
--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 --直接調(diào)用distinct-values函數(shù)來操作
/*
a,b,c,dd,ee,f,aa
*/

-- 導(dǎo)入去重, 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時,用當(dāng)前節(jié)點去//emploee節(jié)點集中找節(jié)點集中empid等于當(dāng)前節(jié)點的empid, 在找到的集合中取最后一個利用last()函數(shù)
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
--同組一選多，也可應(yīng)用此方法，不過沒有必要，就不再累贅了。

--(3)
--====================================================================
--列名,列值相關(guān)
--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為二級節(jié)點(因為文檔本身無根節(jié)點，即為每項的頂級節(jié)點)即為一個r節(jié)點表示一條記錄. r下級節(jié)點，每個表示一個列，因為列名未知，所以用/*匹配所有節(jié)點，因為name為區(qū)別列，不參與聚合運算，故用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)生成