SQL Server 中 XML 數據處理實戰：OPENXML 與 FOR XML 實現行列轉換

引言

SQL Server 2005+ 原生支持 XML 數據類型與豐富的處理函數，使關係型數據與半結構化 XML 能無縫互轉。本文通過實例演示 sp_xml_preparedocument / OPENXML 解析 XML 為行集、FOR XML AUTO/PATH 生成 XML、以及 STUFF + FOR XML PATH 實現「行轉列」聚合的經典模式。

一、OPENXML 解析 XML 為關係行集

1.1 基礎流程

1. sp_xml_preparedocument 將 XML 文本解析為內存 DOM 文檔，返回句柄 @idoc
2. OPENXML(@idoc, xpath, flags) 配合 WITH 子句將節點映射為列
3. sp_xml_removedocument 釋放內存句柄

1.2 範例：解析多狀態條件列表

DECLARE @idoc INT;
DECLARE @doc XML = N'
<condition>
  <State csif_cStateID="S001"/>
  <State csif_cStateID="S002"/>
  <State csif_cStateID="S003"/>
</condition>';

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

SELECT csif_cStateID
FROM OPENXML(@idoc, '/condition/State')
WITH (csif_cStateID VARCHAR(15));

EXEC sp_xml_removedocument @idoc;

輸出：三行 S001、S002、S003。

1.3 範例：解析複雜訂單明細

DECLARE @docHandle INT;
DECLARE @xmlDocument NVARCHAR(MAX) = N'
<Item>
  <SoNo>工程單號</SoNo>
  <ItemID>產品ID</ItemID>
  <EditionCD>版本CD</EditionCD>
  <ImprintCD>出版社CD</ImprintCD>
  <ProductName>產品名稱</ProductName>
  <EditionName>版本</EditionName>
  <ImprintName>出版社</ImprintName>
  <PackType>袋型</PackType>
  <ExFactory>最早走貨期</ExFactory>
  <OrderQty>訂單數量</OrderQty>
  <PlanProductQty>小排期排期數</PlanProductQty>
</Item>';

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

SELECT *
FROM OPENXML(@docHandle, N'/Item', 2)  -- flags=2 表示屬性+元素映射
WITH (
    SoNo            NCHAR(20),
    ItemID          NVARCHAR(10),
    EditionCD       NVARCHAR(10),
    ImprintCD       NVARCHAR(10)
);

EXEC sp_xml_removedocument @docHandle;

二、FOR XML 生成 XML 文檔

2.1 FOR XML AUTO：基於查詢結構自動生成層級

SELECT SoNo, ItemID, EditionCD
FROM OrderItems
FOR XML AUTO, ROOT('OrderList');

輸出：<OrderList><OrderItems SoNo="..." ItemID="..." EditionCD="..."/>...</OrderList>

2.2 FOR XML PATH：完全控制元素/屬性命名與巢狀結構

SELECT 
    SoNo AS '@SoNo',
    ItemID AS 'Item/ItemID',
    EditionCD AS 'Item/EditionCD'
FROM OrderItems
FOR XML PATH('OrderItem'), ROOT('OrderList');

輸出：<OrderList><OrderItem SoNo="..."><Item><ItemID>...</ItemID><EditionCD>...</EditionCD></Item></OrderItem>...</OrderList>

三、行轉列聚合：STUFF + FOR XML PATH 經典模式

場景：同一箱裝產品 (rcpi_iCartonProductID) 對應多個工程單號 (so)，需聚合為逗號分隔字符串。

SELECT 
    a.rcpi_cCartonContentID,
    a.rcpi_iCartonProductID,
    STUFF((
        SELECT ',' + ISNULL(b.so, '')
        FROM #tempOldRcpi b
        WHERE b.rcpi_iCartonProductID = a.rcpi_iCartonProductID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SoList
FROM (
    SELECT DISTINCT rcpi_cCartonContentID, rcpi_iCartonProductID
    FROM #tempOldRcpi
) a;

原理解析：

• 子查詢 FOR XML PATH('') 生成 ,值1,值2,值3 形式 XML 片段
• .value('.', 'NVARCHAR(MAX)') 提取文本內容
• STUFF(..., 1, 1, '') 移除首個逗號

現代替代方案（SQL Server 2017+）：STRING_AGG(ISNULL(so, ''), ',') 更簡潔高效。

四、性能與最佳實踐提示

• 內存管理：sp_xml_preparedocument 佔用內存，大文檔解析後務必調用 sp_xml_removedocument 釋放
• XPath 效率：優先使用具體路徑（/Root/Item）而非 //Item 全樹掃描
• 旗標選擇：flags=1 僅屬性映射、2 屬性+元素、8 僅元素；按需選擇減少開銷
• 大數據量行轉列：STRING_AGG / JSON_ARRAYAGG（2022+）優於 STUFF+FOR XML
• 類型安全：WITH 子句顯式聲明列類型，避免隱式轉換錯誤

五、總結

SQL Server 的 XML 能力已從早期 OPENXML 內存 DOM 解析，演進為原生 XML 數據類型、FOR XML PATH 靈活生成、STRING_AGG 高效聚合的完整工具鏈。根據版本與場景選擇合適方案：舊版本維護用 OPENXML，新開發優先 XML 類型方法、FOR XML PATH、STRING_AGG，兼顧性能與可維護性。