SQL Server FOR XML PATH 完全指南：從 XML 輸出到字串聚合的進階應用

前言

FOR XML PATH 是 SQL Server 將查詢結果序列化為 XML 的強大工具，更因其靈活的節點控制能力，成為 **字串聚合（行轉逗號分隔字串）** 的事實標準寫法。本文從基礎 XML 產生、節點自訂，深入到實戰「學生-愛好」一對多彙總場景。

一、基礎 XML 輸出

DECLARE @hobby TABLE (hobbyID INT, hName NVARCHAR(20));
INSERT @hobby VALUES (1,N'爬山'),(2,N'游泳'),(3,N'美食');

-- 1. 預設  元素，欄位為屬性
SELECT * FROM @hobby FOR XML PATH;

二、自訂行元素名稱

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

三、自訂列元素（屬性 → 子元素）

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

  1
  爬山

...

• 別名加單引號 AS 'Name' 可保留大小寫與特殊字元。
• 若要輸出屬性而非子元素，別名前加 @：hobbyID AS '@MyCode'。

四、ELEMENTS 指令：強制所有欄位輸出為子元素

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

五、根元素包裹：ROOT

SELECT * FROM @hobby FOR XML PATH('MyHobby'), ROOT('HobbyList');

  ...

六、空值處理：ELEMENTS XSINIL

INSERT @hobby VALUES (4, NULL);
SELECT * FROM @hobby FOR XML PATH('MyHobby'), ELEMENTS XSINIL;

  4
  

七、字串聚合核心技巧：PATH('') + 字串串接

-- 輸出：[ 爬山 ][ 游泳 ][ 美食 ]
SELECT '[ ' + hName + ' ]' FROM @hobby FOR XML PATH('');

-- 輸出：{1}[ 爬山 ]{2}[ 游泳 ]{3}[ 美食 ]
SELECT '{' + CAST(hobbyID AS VARCHAR(10)) + '}[ ' + hName + ' ]'
FROM @hobby FOR XML PATH('');

• PATH('') 移除行元素，直接輸出字串內容。
• 非字串型別需 CAST/CONVERT/STR 轉型。

八、實戰場景：學生愛好彙總（一對多行轉字串）

8.1 範例資料

DECLARE @student TABLE (stuID INT, sName NVARCHAR(10), hobby NVARCHAR(20));
INSERT @student VALUES
(1,N'張三',N'爬山'),(1,N'張三',N'游泳'),
(2,N'李四',N'美食'),(2,N'李四',N'閱讀'),(2,N'李四',N'程式');

8.2 核心查詢

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;

8.3 執行流程解析

1. 外層 GROUP BY sName 取得不重複學生清單。
2. 子查詢 SELECT hobby + ',' ... FOR XML PATH('') 將該生所有愛好串成 爬山,游泳,。
3. 外層 LEFT(..., LEN()-1) 切掉最後一個逗號。

8.4 結果

sName  hobby
張三   爬山,游泳
李四   美食,閱讀,程式

九、現代替代方案：STRING_AGG (SQL 2017+)

SELECT sName, STRING_AGG(hobby, ',') AS hobby
FROM @student
GROUP BY sName;

• 語法更簡潔、效能更佳、支援 WITHIN GROUP (ORDER BY ...) 排序。
• 舊版本仍需沿用 FOR XML PATH 技巧。

十、效能與避坑指南

十一、其他 FOR XML 模式速查

• RAW：每列單一 <row .../>，屬性化，適合簡單匯出。
• AUTO：依 JOIN 階層自動巢狀，適合主從表 XML。
• EXPLICIT：完全手工控制結構，複雜度極高，**不建議新專案使用**。