SQL Server 2005 XML 資料操作實戰：OPENXML 導入導出、XQuery 查詢與 XML DML 修改完整指南

前言：資料庫原生 XML 處理的三大核心能力

SQL Server 2005 引入原生 `xml` 資料類型，提供 **儲存、索引、查詢、修改** 一體化能力。本文系統整理三大實務場景：**OPENXML 大量導入 (舊版相容)、XQuery/XPath 查詢、XML DML (Data Modification Language) 修改**，並對比新舊 API 適用場景。

場景一：OPENXML 大量導入 XML 文件 (舊版相容、高效能批次)

適用： 將外部 XML 文件 (如 Web Service 回應、檔案匯入) 批次轉為關聯資料列寫入資料表。**不需資料表具備 `xml` 欄位**。

核心流程：sp_xml_preparedocument → OPENXML → INSERT → sp_xml_removedocument

-- 1. 準備 XML 變數 (可來自參數、檔案讀取、變數)
DECLARE @xmlDoc NVARCHAR(MAX) = N'

  0
  0
  0
';

-- 2. 建立內部文件物件 (佔用記憶體，用畢必須移除)
DECLARE @hDoc INT;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDoc;

-- 3. 使用 OPENXML 行集函數查詢/插入
-- WITH 子句定義欄位對應 (可引用現有資料表結構)
INSERT INTO dbo.XmlTable (Name, NowTime)
SELECT Name, NowTime
FROM OPENXML(@hDoc, '/XmlTables/XmlTable', 2)  -- 2 = 屬性中心對應
WITH (Name NVARCHAR(20), NowTime NVARCHAR(20)); -- 或 WITH dbo.XmlTable

-- 4. 釋放文件物件 (關鍵！避免記憶體洩漏)
EXEC sp_xml_removedocument @hDoc;

多表導入技巧：XPath 路徑切換

-- 同一 XML 文件包含多種實體，以不同 XPath 導入不同表
INSERT INTO dbo.XmlTable2 (Name, NowTime)
SELECT Name, NowTime
FROM OPENXML(@hDoc, '/XmlTables/XmlTable2', 2)
WITH (Name NVARCHAR(20), NowTime NVARCHAR(20));

OPENXML 旗標 (第 3 參數) 對應模式

場景二：原生 xml 欄位的 XQuery / XPath 查詢 (現代標準、索引加速)

前提： 資料表擁有 `xml` 類型欄位 (如 `dbo.Orders.OrderData xml`)。

基本查詢方法

-- 1. exist() : 存在性檢查 (WHERE 子句)
SELECT OrderID FROM dbo.Orders
WHERE OrderData.exist('/Order/Items/Item[@Price > 100]') = 1;

-- 2. value() : 取得單一標量值 (SELECT 子句)
SELECT
    OrderID,
    OrderData.value('(/Order/Customer/@ID)[1]', 'INT') AS CustomerID,
    OrderData.value('(/Order/Total)[1]', 'DECIMAL(18,2)') AS Total
FROM dbo.Orders;

-- 3. query() : 取得 XML 片段 (SELECT 子句)
SELECT OrderData.query('/Order/Items/Item[@Qty > 10]') AS BigItems
FROM dbo.Orders;

-- 4. nodes() : 將 XML 節點撕裂為關聯資料列 (FROM 子句，配合 CROSS APPLY)
SELECT
    O.OrderID,
    Item.value('@ProductID', 'INT') AS ProductID,
    Item.value('@Qty', 'INT') AS Qty,
    Item.value('@Price', 'MONEY') AS Price
FROM dbo.Orders O
CROSS APPLY O.OrderData.nodes('/Order/Items/Item') AS Items(Item);

XML 索引：查詢效能關鍵

-- 主要 XML 索引 (必要)
CREATE PRIMARY XML INDEX IX_Orders_OrderData ON dbo.Orders(OrderData);

-- 次要 XML 索引 (針對特定查詢模式)
CREATE XML INDEX IX_Orders_OrderData_Path ON dbo.Orders(OrderData)
USING XML INDEX IX_Orders_OrderData FOR PATH;   -- 路徑查詢優化
CREATE XML INDEX IX_Orders_OrderData_Value ON dbo.Orders(OrderData)
USING XML INDEX IX_Orders_OrderData FOR VALUE;  -- 值查詢優化
CREATE XML INDEX IX_Orders_OrderData_Property ON dbo.Orders(OrderData)
USING XML INDEX IX_Orders_OrderData FOR PROPERTY; -- 屬性查詢優化

場景三：XML DML (Data Modification Language) 就地修改 XML 內容

優勢： 無需讀取→修改→寫回整份 XML 文件，**僅修改目標節點**，大幅減少 IO、記錄檔、鎖範圍。

三大 DML 陳述式 (需在 `modify()` 方法中使用)

DECLARE @xml XML = N'';

-- 1. INSERT : 插入節點
SET @xml.modify('INSERT  INTO (/Root)[1]');

-- 2. DELETE : 刪除節點
SET @xml.modify('DELETE /Root/Item[@ID=2]');

-- 3. REPLACE VALUE OF : 修改節點值
SET @xml.modify('REPLACE VALUE OF (/Root/Item[@ID=1]/@Price)[1] WITH "15"');

SELECT @xml;
-- 結果: 

應用於資料表更新

UPDATE dbo.Orders
SET OrderData.modify('REPLACE VALUE OF (/Order/Status)[1] WITH "Shipped"')
WHERE OrderID = 12345;

新舊 API 選型指南

結語

SQL Server XML 能力矩陣：**OPENXML 專司高效導入**、**XQuery 專司靈活查詢**、**XML DML 專司精準修改**、**FOR XML 專司標準輸出**。掌握四大支柱適用邊界，配合 `xml` 欄位索引策略，即可在關聯引擎內高效處理半結構化資料，免除應用層繁瑣序列化/解析/往返開銷。