萬盛學電腦網

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

SQL Server FOR XML 完整指南:RAW、AUTO、EXPLICIT 三種模式深度解析

關係資料轉 XML:FOR XML 子句全模式實戰

SQL Server FOR XML 子句將查詢結果直接序列化為 XML,支援三種模式,滿足從簡單匯出到複雜結構化文件生成的各類需求。

語法骨架

SELECT 
FROM 
[WHERE ...]
[ORDER BY ...]
FOR XML 
{ RAW | AUTO | EXPLICIT }
[ , XMLDATA ]      -- 附加 XDR Schema
[ , ELEMENTS ]     -- 屬性改為子元素
[ , BINARY BASE64 ] -- 二進位欄位 Base64 編碼
[ , ROOT('RootName') ] -- 包裝根元素 (2008+)

模式一:RAW - 扁平屬性列表

SELECT TOP 3 OrderID, OrderDate, ProductID
FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID
ORDER BY OrderID
FOR XML RAW;

輸出



  • 每行一個 <row> 元素,欄位為屬性。
  • 可自訂元素名:FOR XML RAW('OrderDetail') → <OrderDetail .../>。
  • 適合:簡單匯出、Web Service 回傳、單層結構。

模式二:AUTO - 依連接關係自動巢狀

SELECT TOP 3 o.OrderID, o.OrderDate, od.ProductID
FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID
ORDER BY o.OrderID
FOR XML AUTO;

輸出


  
  
  
  • 根據 FROM/JOIN 順序自動建立階層:第一張表成父層,後續表成子層。
  • 表名含空格自動編碼 (Order_x0020_Details),建議使用別名:SELECT o.OrderID, od.ProductID FROM Orders o JOIN [Order Details] od ... FOR XML AUTO → <od ProductID="..."/>。
  • 加上 ELEMENTS 選項可將屬性轉為子元素:FOR XML AUTO, ELEMENTS。

模式三:EXPLICIT - 完全控制 (複雜、高維護成本)

需手工構建 Universal Table,包含兩個特殊欄位:

  • Tag:元素標識 (1=根, 2=子...)
  • Parent:父元素 Tag
SELECT 1 AS Tag, NULL AS Parent,
       o.OrderID AS [Order!1!OrderID],
       o.OrderDate AS [Order!1!OrderDate!ELEMENT],
       NULL AS [Detail!2!ProductID]
FROM Orders o
WHERE o.OrderID = 10248
UNION ALL
SELECT 2 AS Tag, 1 AS Parent,
       o.OrderID, NULL, od.ProductID
FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID
WHERE o.OrderID = 10248
FOR XML EXPLICIT;

輸出


  1996-07-04T00:00:00
  
  
  

適用: 極度複雜、非標準 XML 結構、需混合屬性/元素、CDATA、處理指令等場景。**維護成本極高,非必要勿用。**

可選修飾詞

選項效果適用模式
XMLDATA結果前附加 XDR Schema (已淘汰,建議用 XSD)RAW, AUTO, EXPLICIT
ELEMENTS所有欄位輸出為子元素而非屬性AUTO, RAW (EXPLICIT 由欄位別名控制)
BINARY BASE64二進位欄位 (VARBINARY, IMAGE) 以 Base64 編碼所有
ROOT('RootName')包裝單一根元素 (解決多頂層元素非 Well-formed 問題)2008+ 所有模式
TYPE回傳 XML 型別而非 NVARCHAR(MAX),可進一步查詢所有

進階:OPENXML 反向解析 (XML → 關係表)

DECLARE @xml XML = '';
DECLARE @hDoc INT;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml;

SELECT * FROM OPENXML(@hDoc, '/Orders/Order/Detail', 2)
WITH (OrderID INT '../@ID', ProductID INT '@PID');

EXEC sp_xml_removedocument @hDoc;

最佳實踐建議

  1. **優先用 AUTO + ELEMENTS + ROOT** 產生標準化文件,結構清晰、無需手工維護。
  2. **大量資料流式輸出** 使用 FOR XML RAW, TYPE 配合 sqlcmd -y0 或應用層流式讀取 XMLReader,避免記憶體爆炸。
  3. **命名空間支援** (2005+):WITH XMLNAMESPACES (DEFAULT 'urn:myschema') SELECT ... FOR XML AUTO。
  4. **效能**:FOR XML 在引擎層序列化,比應用層組裝快 10 倍以上,但大結果集仍會佔用大量記憶體,建議分頁輸出。
copyright © 萬盛學電腦網 all rights reserved