SQL 中的For Xml Path詳解

2022-08-03 14:08:40

FOR XML PATH是什麼

FOR XML PATH 是將查詢結果集以XML形式展現，將多行的結果，展示在同一行。

我們用範例來給大家介紹它的神奇之處。

建立測試資料

我們建立一個統計學生愛好的表

CREATE TABLE Stu_Hobby(
Stu_Name NVARCHAR(20),--姓名
Age INT,--年齡
Hobby NVARCHAR(20)  --愛好
)
INSERT INTO Stu_Hobby
VALUES  ( N'張三',19,N'踢足球'),
 ( N'張三',19,N'打籃球'),
 ( N'張三',19,N'游泳'),
 ( N'李四',21,N'看電影'),
 ( N'李四',21,N'閱讀'),
 ( N'王五',22,N'唱歌'),
 ( N'王五',22,N'玩遊戲'),
 ( N'馬六',19,N'踢足球'),
 ( N'趙七',20,N'爬山'),
 ( N'趙七',20,N'跑步')

查詢學生愛好表Stu_Hobby裡面的資料:

使用方法介紹

測試資料建立好後，我們開始對這個表裡面的資料進行查詢，並使用上FOR XML PATH。

SELECT *FROM dbo.Stu_Hobby FOR XML PATH;

結果如下：

它會生成一段XML程式碼，我們點選這行程式碼會彈出一整個XML的頁面，由於篇幅較長，我們只擷取一部分，具體如下：

此外我們還可以在FOR XML PATH的後面寫引數，如果後面接引數，會將節點換成引數名稱，例如：

SELECT *
FROM dbo.Stu_Hobby FOR XML PATH(hobby)

結果如下圖：

已經變成了我們新增的引數了。

跟我們實際需求相接近的是下面這個功能

我們可以單獨輸出某個欄位的值，例如我們想看看學生愛好表中Hobby這一列具體有一些什麼值，可以這樣寫：

SELECT Hobby+'、'
FROM dbo.Stu_Hobby FOR XML PATH('')

注意：上面的+是欄位拼接，就是將兩個字串用+連成一個字串。然後我們把XML中的給去掉。

結果如下：

可以看到我們寫的所有愛好都給列出來了，沒有去掉重複的，可以理解成把列裡的值都顯示出來了。

具體實際應用

我們現在想把上面的學生表裡每個學生的愛好單獨顯示一行，愛好用"、"隔開。

SELECT
A.Stu_Name,
A.Age,
(SELECT Hobby+'、'  
FROM [dbo].Stu_Hobby
WHERE
--必須加的匹配條件
Stu_Name=A.Stu_Name AND Age=A.Age  
FOR XML PATH('')) AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age

見證奇蹟的時刻到了！！！

對比我們先前建的表，這裡已經將Hobby列的資料按每個學生變成了一行。

上面的WHERE條件是必須要的，如果去掉會怎麼樣呢？我們把WHERE條件註釋掉看看會怎麼樣？

SELECT
A.Stu_Name,
A.Age,
(SELECT Hobby+'、'
FROM [dbo].Stu_Hobby
--WHERE
--必須加的匹配條件
--Stu_Name=A.Stu_Name AND Age=A.Age
FOR XML PATH('')) AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age

結果如下：

就會將Hobby列所有值都顯示出來，很顯然這不是我們要的結果

程式碼優化

不知道小夥伴們有沒有發現Hobby列的結果尾部多了一個"、"，看著好彆扭，有沒有什麼辦法將它去掉呢？答案是肯定的。

先用一個LEFT()和LEN()函數來處理一下Hobby列

SELECT
T.Stu_Name,
T.Age,
LEFT(T.Hobby,LEN(T.Hobby)-1) AS Hobby
FROM
(SELECT
A.Stu_Name,
A.Age,
(SELECT Hobby+'、'
FROM [dbo].Stu_Hobby
WHERE
--必須加的匹配條件
Stu_Name=A.Stu_Name AND Age=A.Age
FOR XML PATH('')) AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age
) T

結果如下：

這樣我們的需求就得到了完美解決，但是這個程式碼有點長額，能不能簡短一點呀？答案也是肯定滴！在將程式碼精簡之前，我們需要先給大家介紹一個配合使用的函數：

STUFF()

STUFF()函數的作用

STUFF()函數用於刪除指定長度的字元，並可以在指定的起點處插入另一組字元。STUFF()函數中如果開始位置或長度值是負數，或者如果開始位置大於第一個字串的長度，將返回空字串。如果要刪除的長度大於第一個字串的長度，將刪除到第一個字串中的第一個字元。

STUFF()函數的語法

STUFF ( character_expression , start , length ,character_expression )

引數解釋

character_expression：一個字元資料表示式。character_expression 可以是常數、變數，也可以是字元列或二進位制資料列。

start ：一個整數值，指定刪除和插入的開始位置。如果 start 或 length 為負，則返回空字串。如果 start 比第一個 character_expression 長，則返回空字串。start 可以是 bigint 型別。

length：一個整數，指定要刪除的字元數。如果 length 比第一個 character_expression 長，則最多刪除到最後一個 character_expression 中的最後一個字元。length 可以是 bigint 型別。

返回型別

如果 character_expression 是受支援的字元資料型別，則返回字元資料。如果 character_expression 是一個受支援的 binary 資料型別，則返回二進位制資料。

注意事項

1.如果開始位置或長度值是負數，或者如果開始位置大於第一個字串的長度，將返回空字串。如果要刪除的長度大於第一個字串的長度，將刪除到第一個字串中的第一個字元。

2.如果結果值大於返回型別支援的最大值，則產生錯誤。

--以上資訊來源微軟官方檔案

這定義看的頭暈，我們還是來看看怎麼使用吧

範例：

SELECT STUFF('abcdefg',1,0,'1234') --結果為'1234abcdefg'
SELECT STUFF('abcdefg',1,1,'1234') --結果為'1234bcdefg'
SELECT STUFF('abcdefg',2,1,'1234') --結果為'a1234cdefg'
SELECT STUFF('abcdefg',2,2,'1234') --結果為'a1234defg'

說了這麼多，我們看看STUFF怎麼解決我們上面的問題吧，上程式碼：

SELECT
A.Stu_Name,
A.Age,
STUFF(
(SELECT '、'+Hobby
FROM [dbo].Stu_Hobby
WHERE
--必須加的匹配條件
Stu_Name=A.Stu_Name AND Age=A.Age    
FOR XML PATH('')
),1,1,'') AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age

是不是比LEFT簡短一些啦？我們看一下結果是不是我們想要的。