FOR XML PATH 函數用法

一.FOR XML PATH 基本介紹

       有時我們進入APP會有調查登記，有一個或以上可選擇愛好，那我們現在用FOR XML PATH來顯示用戶的愛好情況，假設有成員表member ，如下圖所示：

 

 

 

語句SELECT * FROM MEMBER查詢出來結果如上圖所示，現加入FOR XML PATH看下會有什么變化，語句如下：

 

 

 

點擊進入可查詢詳細情況

 

 

由此可見，FOR XML PATH 是可以將查詢結果以XML格式輸出。

那么，我們可以自定義節點嗎？先試試

SELECT * FROM MEMBER FOR XML PATH ('m')

 

 

結果顯示行節點變成了<m>，既然可以修改，那當然path()內的值可以修改成其他值。

如果是這樣path('')則表示去掉行節點。

那列節點呢？回顧下SELECT  *或 SELECT 列名 ，那也就是說可以從這里改變它

 

 

但上面這樣輸出的形式還不是我們想要看到的，怎么能把一些符號可以去掉呢？

讓我們自定義輸出，語句如下：

SELECT '[' 愛好 ']' FROM MEMBER FOR XML PATH('')

輸出結果：

因為path('')值為空，所以結果只會顯示一行一個值記錄

如果是int數據類型，如年齡，需要轉換成字符串

 

 

FOR XML PATH基本使用就介紹到這里，下面看下如何運用

二、結合GROUP BY 實際應用，要求按下圖方式顯示

 

 

分析下看如何運用，除了與GROUP BY 結合使用外還用到STUFF函數，當然也可以用LEFT等函數，我這里只簡單介紹STUFF函數的使用

STUFF函數作用：刪除指定長度的字符，或在指定的起點處插入另一組字符。

STUFF ( character_expression , start , length , replaceWith_expression )

character_expression表示：字符數據的表達式

start表示：一個整數值，指定刪除和插入的開始位置

length表示：一個整數值，指定要刪除的字符數

 replaceWith_expression表示：字符數據的表達式

先看以下語句：

select a.編號,a.姓名,愛好=(select ',' 愛好 from member where 編號=a.編號 for xml path(''))from member agroup by a.編號,a.姓名

溫馨提示：上述語句嵌套相關子查詢將FOR XML PATH結果數據賦值給父查詢“愛好”列

輸出結果：

 

 

注意：必須要加上條件where 編號=a.編號，要不然會以下結果輸出

 

 

現在要想辦法去掉“愛好”列的第一個逗號，此時用到STUFF函數

select a.編號,a.姓名,愛好=STUFF((select ',' 愛好 from member where 編號=a.編號 for xml path('')),1,1,'')from member aGROUP BY a.編號,a.姓名

輸出結果：

 

 

STUFF((select ',' 愛好 from member where 編號=a.編號 for xml path('')),1,1,'')

注釋：STUFF函數將子查詢“愛好”的字符串內容，從第1個字符開始截取，取長度是1，刪除它，然后把結果賦值給父查詢的列。