SQL Server XML 查詢深度解析：FOR XML AUTO 模式完整指南

前言

SQL Server 提供四種 XML 模式：RAW、AUTO、EXPLICIT、PATH。本文聚焦 FOR XML AUTO，透過實例解析其巢狀邏輯、表順序敏感性、試探演算法與特殊資料型別行為。

基礎範例：單一資料表

WITH TestXml AS (
    SELECT 1 AS id, N'LeeWhoeeUniversity' AS name
    UNION ALL SELECT 2, N'DePaul'
    UNION ALL SELECT 3, NULL
)
SELECT id, name FROM TestXml FOR XML AUTO;

結果：

<TestXml id="1" name="LeeWhoeeUniversity" />
<TestXml id="2" name="DePaul" />
<TestXml id="3" />

• 資料表名稱 (或 CTE 名稱) 成為元素名稱，取代 RAW 模式的 ``。
• 欄位對應為屬性 (Attribute-centric)；若加 `ELEMENTS` 則輸出子元素。
• NULL 值預設略過屬性 (可加 `XSINIL` 輸出 `xsi:nil="true"`)。

多表聯結：父子巢狀結構

WITH [Order] AS (
    SELECT 122 AS orderid, 1 AS productid, 10 AS quantity
    UNION ALL SELECT 123, 1, 100
    UNION ALL SELECT 124, 2, 20
    UNION ALL SELECT 125, 3, 5
),
Product AS (
    SELECT 1 AS id, N'LeeWhoeeUniversity' AS name
    UNION ALL SELECT 2, N'DePaul'
)
SELECT * 
FROM Product, [Order] 
WHERE [Order].productid = Product.id 
FOR XML AUTO;

結果：

<Product id="1" name="LeeWhoeeUniversity">
  <Order orderid="122" productid="1" quantity="10" />
  <Order orderid="123" productid="1" quantity="100" />
</Product>
<Product id="2" name="DePaul">
  <Order orderid="124" productid="2" quantity="20" />
</Product>

• 表順序決定巢狀層級：`FROM Product, [Order]` 使 Product 成為父層，Order 成為子層。
• 若交換順序 `FROM [Order], Product`，則 Order 變父層，Product 變子層 (見原文片段 3)。

AUTO 模式試探演算法

SQL Server 依據 結果集欄位值逐列比較 決定巢狀：

1. 比較父層資料表 (Product) 所有非 LOB 欄位 (id, name)。
2. 相鄰列若父層欄位值全相同 → 合併為同一父元素下的多個子元素。
3. 若父層欄位值改變 → 關閉前一父元素，開啟新父元素。

關鍵限制：ntext, text, image, xml 及 `(n)varchar(max)`, `varbinary(max)` 不參與比較，視為永遠不同 → 導致相同邏輯值被拆分為多個父元素 (見「text 型別特殊」範例)。

結果集排序對試探的影響

若 `ORDER BY` 導致相同父層鍵值的列不相鄰，試探演算法會產生重複父元素 (見原文「結果集排序對 AUTO 試探的影響」)。
最佳實務：確保父層鍵值欄位包含在 `ORDER BY` 最前段，使相同父實體的列連續出現。

進階選項

• ELEMENTS：輸出子元素而非屬性。
• BINARY BASE64：二進位資料以 Base64 編碼輸出。
• TYPE：回傳 `xml` 型別而非 `nvarchar(max)`，可進一步用 XQuery 處理。
• ROOT('RootName')：包裝單一根元素，產出完整 XML 文件。

效能與替代方案建議

• AUTO 模式便利但輸出結構隱含邏輯，難以精確控制；複雜結構建議改用 FOR XML PATH (SQL 2005+) 或 FOR XML EXPLICIT (舊版)。
• 大量資料產生 XML 時，考慮分頁或流式處理，避免記憶體壓力。