萬盛學電腦網

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

SQL Server 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" />
  • **表名/別名** 成為元素名(替代 RAW 模式的 <row>)。
  • 列值作為**屬性**輸出(預設),NULL 列省略。

多表連接:自動巢狀層級

WITH [Order] AS (
    SELECT 122 AS OrderID, 1 AS ProductID, 10 AS Qty
    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" Qty="10" />
  <Order OrderID="123" ProductID="1" Qty="100" />
</Product>
<Product ID="2" Name="DePaul">
  <Order OrderID="124" ProductID="2" Qty="20" />
</Product>
  • **FROM 子句表順序決定層級**:第一張表(Product)成父元素,後續表(Order)成子元素。
  • 連接條件隱含父子關係,相同父鍵值的行自動聚合為同一父元素下的多個子元素。

表順序敏感性驗證

交換 FROM 順序:SELECT * FROM [Order], Product ...

輸出反轉:<Order> 成父,<Product> 成子。**務必按業務邏輯「一」方在前、「多」方在後排列。**

AUTO 模式試探算法

  1. 比較相鄰行的**非大對象類型列值**(含 (N)VARCHAR(MAX), VARBINARY(MAX))。
  2. 若前一行與當前行在**父表所有列**上值相同 → 視為同一父實體,子元素追加。
  3. 若有差異 → 關閉前父元素,開啟新父元素。

TEXT/NTEXT/IMAGE/XML 類型的特殊行為

這些類型**不參與試探比較**,視為永遠不同。導致同邏輯父實體被拆分為多個父元素。

-- 將 Name 改為 TEXT 類型
DECLARE @Product TABLE (ID INT, Name TEXT);
-- 結果:相同 Name 的 Product 被拆分為兩個父元素,各含一個 Order 子元素

對策:避免在 FOR XML AUTO 查詢的父表投影中使用大對象類型,或改用 FOR XML PATH 顯式控制結構。

結果集排序對輸出結構的影響

若 ORDER BY 導致同父鍵行不連續,試探算法會誤判為不同父實體,產生重複父元素。

-- 修改 OrderID 使同 ProductID 行不相鄰
SELECT * FROM Product, [Order] ... ORDER BY OrderID;
-- AUTO 輸出:Product 1 → Order 122 → Product 2 → Order 123 → Product 1 → Order 125

最佳實踐:ORDER BY 父表主鍵, 子表排序鍵,保證同父鍵行物理連續。

擴展選項

  • ELEMENTS:列輸出為子元素而非屬性。
  • BINARY BASE64:二進制列 Base64 編碼。
  • ROOT('RootName'):包裹單一頂層元素。
copyright © 萬盛學電腦網 all rights reserved