SQL Server FOR XML 子句將查詢結果直接序列化為 XML,支援三種模式,滿足從簡單匯出到複雜結構化文件生成的各類需求。
SELECTFROM [WHERE ...] [ORDER BY ...] FOR XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] -- 附加 XDR Schema [ , ELEMENTS ] -- 屬性改為子元素 [ , BINARY BASE64 ] -- 二進位欄位 Base64 編碼 [ , ROOT('RootName') ] -- 包裝根元素 (2008+)
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 .../>。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。需手工構建 Universal Table,包含兩個特殊欄位:
Tag:元素標識 (1=根, 2=子...)Parent:父元素 TagSELECT 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),可進一步查詢 | 所有 |
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;
FOR XML RAW, TYPE 配合 sqlcmd -y0 或應用層流式讀取 XMLReader,避免記憶體爆炸。WITH XMLNAMESPACES (DEFAULT 'urn:myschema') SELECT ... FOR XML AUTO。FOR XML 在引擎層序列化,比應用層組裝快 10 倍以上,但大結果集仍會佔用大量記憶體,建議分頁輸出。