萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server FOR XML PATH 完全指南:從 XML 輸出到字串聚合實戰

什麼是 FOR XML PATH?

FOR XML PATH 可將查詢結果以 XML 格式輸出,並支援自訂節點名稱與結構。更強大的是:配合空路徑 PATH('') 可實現字串聚合(如將多行合併為逗號分隔字串),替代舊版需用游標/函數的複雜邏輯。

一、基礎 XML 輸出

範例資料:興趣愛好表

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

預設輸出

SELECT * FROM @hobby FOR XML PATH;
-- 1爬山...

自訂列節點名稱

SELECT * FROM @hobby FOR XML PATH('MyHobby');
-- 1爬山...

自訂欄位節點名稱

SELECT hobbyID AS 'MyCode', hName AS 'MyName' 
FROM @hobby FOR XML PATH('MyHobby');
-- 1爬山...

二、字串聚合:多行合併為單一字串

核心技巧:PATH('') + 字串串接

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

混合型別轉字串

SELECT '{' + STR(hobbyID) + '}', '[ ' + hName + ' ]' 
FROM @hobby FOR XML PATH('');
-- {1}[ 爬山 ]{2}[ 游泳 ]{3}[ 美食 ]

三、實戰場景:學生愛好彙總

資料結構

DECLARE @student TABLE (stuID INT, sName NVARCHAR(20), hobby NVARCHAR(50));
INSERT @student VALUES 
(1, N'張三', N'爬山'), (2, N'張三', N'游泳'), (3, N'張三', N'美食'),
(4, N'李四', N'閱讀'), (5, N'李四', N'音樂');

查詢:每位學生的愛好以逗號分隔

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;

結果

sName  hobby
張三   爬山,游泳,美食
李四   閱讀,音樂

邏輯解析

  1. 內層子查詢:依學生姓名分組,FOR XML PATH('') 將該生愛好串成 爬山,游泳,美食,
  2. 外層 LEFT(..., LEN()-1) 移除尾端多餘逗號
  3. GROUP BY sName 確保每生僅一行

四、進階:去除重複值

SELECT sName,
       STUFF((
           SELECT DISTINCT ',' + hobby 
           FROM @student 
           WHERE sName = A.sName 
           FOR XML PATH('')), 1, 1, '') AS hobby
FROM @student A
GROUP BY sName;

STUFF 移除首字元逗號,比 LEFT/LEN 更優雅。

五、注意事項

  • XML 輸出會自動編碼特殊字元(<→<),若需原始字串請用 TYPE 指示搭配 .value() 提取
  • 大量資料聚合時,考慮效能,必要時改用 STRING_AGG (SQL 2017+)
  • FOR XML PATH 回傳型別為 XML 或 NVARCHAR(MAX),視用法而定

總結

FOR XML PATH 不僅是 XML 產生器,更是 SQL Server 2005-2016 時代字串聚合的標準解法。掌握 PATH('')、AS 別名、STUFF 三大技巧,即可輕鬆解決「多行合一行」的經典需求。

copyright © 萬盛學電腦網 all rights reserved