查詢 SQL Server 中的 XML 開放內容：FOR XML EXPLICIT 與 XML 視圖實戰

什麼是開放內容？

開放內容指 XML 文件中「未在 Schema 定義」的元素與屬性。實務上，系統只處理已知結構，其餘內容需原封不動保存並轉傳給下游 (如物流商、合作夥伴)。SQL Server 2000 提供兩種主流方式查詢並輸出含開放內容的 XML。

方法一：FOR XML EXPLICIT + XMLTEXT 指示詞

FOR XML EXPLICIT 透過特殊別名結構控制輸出形狀。關鍵在於第四欄位指示詞 XMLTEXT，將資料行內的 XML 片段直接合併至輸出樹中。

範例：訂單行項目含產品開放內容

SELECT
    1       AS Tag,
    NULL    AS Parent,
    OrderID AS [Order!1!OrderID],
    NULL    AS [Item!2!ProductID!ELEMENT],
    NULL    AS [Item!2!Qty!ELEMENT],
    NULL    AS [Item!2!!XMLTEXT]  -- 這裡放開放內容
FROM Orders
WHERE OrderID = 10248
UNION ALL
SELECT
    2,
    1,
    OrderID,
    ProductID,
    Quantity,
    Overflow  -- 產品表中儲存的 XML 片段欄位
FROM [Order Details] od
JOIN Products p ON od.ProductID = p.ProductID
WHERE OrderID = 10248
ORDER BY [Order!1!OrderID], [Item!2!ProductID]
FOR XML EXPLICIT;

輸出片段

<Order OrderID="10248">
  <Item ProductID="11" Qty="12">
    <Weight>1.2</Weight>
    <QuantityPerUnit>12 boxes</QuantityPerUnit>
    <CustomAttr>SpecialPack</CustomAttr>
  </Item>
  ...
</Order>

Overflow 欄位 (型別 ntext/xml) 存放未定義結構的 XML，XMLTEXT 指示詞將其節點直接提升至 Item 元素下，達成「已知結構 + 開放內容」混合輸出。

方法二：XML 視圖 (XSD 對應結構描述) + XPath 查詢

1. 建立 .xsd 對應結構描述，使用 sql:overflow-field 標註開放內容欄位。
2. 透過 CREATE VIEW 或虛擬目錄註冊視圖。
3. 用 XPath (SELECT ... FROM view WHERE ...) 查詢，SQL Server 自動合併開放內容。

XSD 片段

<ElementType name="Order" sql:relation="Orders">
  <AttributeType name="OrderID" />
  <Attribute type="OrderID" />
  <ElementType name="Item" sql:relation="Order Details">
    <AttributeType name="ProductID" />
    <Attribute type="ProductID" />
    <ElementType name="Overflow" sql:field="Overflow" sql:overflow-field="1" />
  </ElementType>
</ElementType>

選擇建議

現代化替代方案 (SQL Server 2005+)

• FOR XML PATH：語法簡潔、支援巢狀子查詢，取代 EXPLICIT 多數用途。
• XML 資料型別 + XQuery：@xml.nodes(), .query(), .value() 原生處理開放內容 (//*[not(namespace-uri())] 等)。
• OPENJSON / FOR JSON：若下游可接受 JSON，開發效率更高。