參考資料:

http://www.rzrgm.cn/doubleliang/archive/2011/07/06/2098775.html

http://www.rzrgm.cn/codeyu/archive/2010/05/25/1743474.html

核心摘要

FOR XML PATH 的作用是把結(jié)果以xml文本的形式顯示出來(lái)，也就是說(shuō)，最終結(jié)果就是一個(gè)字符串，因此我們就不需要使用什么字符串合并函數(shù)了。

STUFF函數(shù)的原型是 Stuff(str1, start, len, str2)，作用是，刪掉str1中start開(kāi)始的len個(gè)字符，用str2替換。因此，可以起到在多個(gè)項(xiàng)之間插入分隔符。

比如，

select ','+name from student for xml path('') group by class

輸出結(jié)果可能是

,Jim,Kate,Tom,Sally

如果使用STUFF，可以刪掉第一個(gè)空格

stuff(select ','+name from student for xml path('') group by class, 1, 1, '')

原文內(nèi)容

-------------參考------------------------

----假設(shè)我們有如下數(shù)據(jù)表
------菜系表------------
--店名        菜系------
--澳門(mén)豆撈    川菜 
--澳門(mén)豆撈    粵菜 
--澳門(mén)豆撈    閩菜 
--為民大酒店  魯菜 
--為民大酒店  粵菜 
--為民大酒店  川菜
-----------------------

-------執(zhí)行如下語(yǔ)句
select 店名,菜系=STUFF((select ','+ltrim(菜系) from 菜系表 where 店名=t.店名 for XML path('')),1,1,'')
from 菜系表 t
group by 店名

-------結(jié)果如下-------------
--店名        菜系----------
--澳門(mén)豆撈    川菜,閩菜,粵菜
--為民大酒店  川菜,魯菜,粵菜
----------------------------

--------------------------------------------------------------------------------------
Stuff(expression1_Str,startIndex,lengthInt,expression2_Str)函數(shù)共有四個(gè)參數(shù)，
其功能是將expression1_Str中自startIndex位置起刪除lengthInt個(gè)字符，然后將expression2插入到expression1_Str中的startIndex位置。startIndex 從1開(kāi)始
數(shù)據(jù)庫(kù)表中有三個(gè)字段，要以xxxx-xxxxxx-xxxxxx的格式輸出，其中不滿相應(yīng)位數(shù)的，在后面補(bǔ)空格，即把類似23，1234，879這樣的三個(gè)數(shù)以如下形式輸出到報(bào)表中：23__-1234__-879___的格式輸出,標(biāo)注顏色的下劃線一種顏色代表一個(gè)空格。 
STUFF(SPACE(4),1,LEN(‘23’),’23’)+’-’+STUFF(SPACE(6),1,LEN(‘1234’),’1234’)+’-’+ STUFF(SPACE(6),1,LEN(‘879’),’879’),轉(zhuǎn)換結(jié)果自然就為23__-1234__-879___了。
Stuff函數(shù)另一種解釋
select stuff('string1',<starting position>,<length to delete from string 1>,'string2')
示例：
select stuff('youxiaofeng',2,1,'F')
從第二個(gè)字符開(kāi)始，刪掉一個(gè)字符，然后用'F'代替刪掉字符的位置，如果不需要?jiǎng)h掉字符，則將<length to delete from string 1>設(shè)為0即可。
注意：如果<starting position>或<length to delete from string 1 >是負(fù)的，或者<starting position>是大于<string 1>長(zhǎng)度的數(shù)，則STUFF函數(shù)將返回NULL,如果指令的<length to delete from string 1>長(zhǎng)于從<starting position>位置到<string 1>結(jié)尾的字符數(shù)，此函數(shù)將<string 1 >在<starting position-1>處截?cái)?br>
---------------------------------------------------------------------------
FOR XML PATH 語(yǔ)句的應(yīng)用
大家都知道在SQL Server中利用 FOR XML PATH 語(yǔ)句能夠把查詢的數(shù)據(jù)生成XML數(shù)據(jù)，下面是它的一些應(yīng)用示例。

DECLARE @TempTable table(UserID int , UserName nvarchar(50));
insert into @TempTable (UserID,UserName) values (1,'a')
insert into @TempTable (UserID,UserName) values (2,'b')

select UserID,UserName from @TempTable FOR XML PATH
運(yùn)行這段腳本，將生成如下結(jié)果：

<row>
<UserID>1</UserID>
<UserName>a</UserName>
</row>
<row>
<UserID>2</UserID>
<UserName>b</UserName>
</row>
大家可以看到兩行數(shù)據(jù)生成了兩個(gè)節(jié)點(diǎn)，修改一下PATH的參數(shù)：

select UserID,UserName from @TempTable FOR XML PATH('lzy')
再次運(yùn)行上述腳本，將生成如下的結(jié)果：

<lzy>
<UserID>1</UserID>
<UserName>a</UserName>
</lzy>
<lzy>
<UserID>2</UserID>
<UserName>b</UserName>
</lzy>
可以看到節(jié)點(diǎn)變成，其實(shí)PATH() 括號(hào)內(nèi)的參數(shù)是控制節(jié)點(diǎn)名稱的，這樣的話大家可以看一下如果是空字符串（不是沒(méi)有參數(shù)）會(huì)是什么結(jié)果?

select UserID,UserName from @TempTable FOR XML PATH('')
執(zhí)行上面這段腳本將生成結(jié)果：

<UserID>1</UserID>
<UserName>a</UserName>
<UserID>2</UserID>
<UserName>b</UserName>
這樣就不顯示上級(jí)節(jié)點(diǎn)了，大家知道在 PATH 模式中，列名或列別名被作為 XPath 表達(dá)式來(lái)處理，也就是說(shuō)，是列的名字，這樣大膽試驗(yàn)一下不給指定列名和別名會(huì)是怎么樣？

select CAST(UserID AS varchar) + '',UserName + '' from @TempTable FOR XML PATH('')
運(yùn)行上面這句將生成結(jié)果

1a2b

所有數(shù)據(jù)都生成一行，而且還沒(méi)有連接字符，這樣的數(shù)據(jù)可能對(duì)大家沒(méi)有用處，還可以再變化一下：

select CAST(UserID AS varchar) + ',',UserName + '',';' from @TempTable FOR XML PATH('')
生成結(jié)果

1,a;2,b;

大家現(xiàn)在明白了吧，可以通過(guò)控制參數(shù)來(lái)生成自己想要的結(jié)果，例如：

select '{' + CAST(UserID AS varchar) + ',','"' +UserName + '"','}' from @TempTable FOR XML PATH('')
生成結(jié)果

{1,"a"}{2,"b"}

還可以生成其他格式，大家可以根據(jù)自己需要的格式進(jìn)行組合。

下面是一個(gè)數(shù)據(jù)統(tǒng)計(jì)的應(yīng)用，希望大家可以通過(guò)下面的實(shí)例想到更多的應(yīng)用

DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into @T1 (UserID,UserName,CityName) values (5,'e','上海')

SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) FROM (
SELECT CityName,
(SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName FOR XML PATH('')) AS UserList
FROM @T1 A 
GROUP BY CityName
) B
生成結(jié)果（每個(gè)城市的用戶名）

北京 b,d
上海 a,c,e

靈活運(yùn)用 SQL SERVER FOR XML PATH

        FOR XML PATH 有的人可能知道有的人可能不知道，其實(shí)它就是將查詢結(jié)果集以XML形式展現(xiàn)，有了它我們可以簡(jiǎn)化我們的查詢語(yǔ)句實(shí)現(xiàn)一些以前可能需要借助函數(shù)活存儲(chǔ)過(guò)程來(lái)完成的工作。那么以一個(gè)實(shí)例為主.

        一.FOR XML PATH 簡(jiǎn)單介紹

             那么還是首先來(lái)介紹一下FOR XML PATH ，假設(shè)現(xiàn)在有一張興趣愛(ài)好表（hobby）用來(lái)存放興趣愛(ài)好，表結(jié)構(gòu)如下：

       接下來(lái)我們來(lái)看應(yīng)用FOR XML PATH的查詢結(jié)果語(yǔ)句如下：

SELECT * FROM @hobby FOR XML PATH

       結(jié)果：

<row>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</row>
<row>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</row>
<row>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</row>

      由此可見(jiàn)FOR XML PATH 可以將查詢結(jié)果根據(jù)行輸出成XML各式！

      那么，如何改變XML行節(jié)點(diǎn)的名稱呢？代碼如下：     

SELECT * FROM @hobby FOR XML PATH('MyHobby')

 

      結(jié)果一定也可想而知了吧？沒(méi)錯(cuò)原來(lái)的行節(jié)點(diǎn)<row> 變成了我們?cè)赑ATH后面括號(hào)()中，自定義的名稱<MyHobby>,結(jié)果如下：

<MyHobby>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</MyHobby>
<MyHobby>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</MyHobby>
<MyHobby>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</MyHobby>

      這個(gè)時(shí)候細(xì)心的朋友一定又會(huì)問(wèn)那么列節(jié)點(diǎn)如何改變呢？還記的給列起別名的關(guān)鍵字AS嗎？對(duì)了就是用它!代碼如下：

SELECT hobbyID as 'MyCode',hName as 'MyName' FROM @hobby FOR XML PATH('MyHobby')

 

      那么這個(gè)時(shí)候我們列的節(jié)點(diǎn)名稱也會(huì)編程我們自定義的名稱 <MyCode>與<MyName>結(jié)果如下：

<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>

    噢！ 既然行的節(jié)點(diǎn)與列的節(jié)點(diǎn)我們都可以自定義，我們是否可以構(gòu)建我們喜歡的輸出方式呢？還是看代碼： 

SELECT '[ '+hName+' ]' FROM @hobby FOR XML PATH('')

    沒(méi)錯(cuò)我們還可以通過(guò)符號(hào)+號(hào)，來(lái)對(duì)字符串類型字段的輸出格式進(jìn)行定義。結(jié)果如下：

[ 爬山 ][ 游泳 ][ 美食 ]

    那么其他類型的列怎么自定義？ 沒(méi)關(guān)系，我們將它們轉(zhuǎn)換成字符串類型就行啦！例如：

SELECT '{'+STR(hobbyID)+'}','[ '+hName+' ]' FROM @hobby FOR XML PATH('')

    好的 FOR XML PATH就基本介紹到這里吧，更多關(guān)于FOR XML的知識(shí)請(qǐng)查閱幫助文檔！

    接下來(lái)我們來(lái)看一個(gè)FOR XML PATH的應(yīng)用場(chǎng)景吧！那么開(kāi)始吧。。。。。。

        二.一個(gè)應(yīng)用場(chǎng)景與FOR XML PATH應(yīng)用

        首先呢！我們?cè)谠黾右粡垖W(xué)生表，列分別為（stuID,sName,hobby）,stuID代表學(xué)生編號(hào)，sName代表學(xué)生姓名，hobby列存學(xué)生的愛(ài)好！那么現(xiàn)在表結(jié)構(gòu)如下：

           

        這時(shí)，我們的要求是查詢學(xué)生表，顯示所有學(xué)生的愛(ài)好的結(jié)果集，代碼如下：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B 

         結(jié)果如下:

 分析： 好的，那么我們來(lái)分析一下，首先看這句：

SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')

這句是通過(guò)FOR XML PATH 將某一姓名如張三的愛(ài)好，顯示成格式為：“ 愛(ài)好1，愛(ài)好2，愛(ài)好3，”的格式！

那么接著看：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B  

剩下的代碼首先是將表分組，在執(zhí)行FOR XML PATH 格式化，這時(shí)當(dāng)還沒(méi)有執(zhí)行最外層的SELECT時(shí)查詢出的結(jié)構(gòu)為:

可以看到StuList列里面的數(shù)據(jù)都會(huì)多出一個(gè)逗號(hào)，這時(shí)隨外層的語(yǔ)句:SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby  就是來(lái)去掉逗號(hào)，并賦予有意義的列明！

好啦，太晚啦就說(shuō)到這里吧！