前言:
??????? 此文只講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

待續。。

來源：http://blog.csdn.net/fcuandy/article/details/3248618
《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的SQLServer 2005 XML 在 T-SQL 查询中的典型应用[转]的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。