SQL Server 讀取與處理 XML 檔案的實作方法

SQL Server 2000 雖然支援將資料匯出為 XML，但要從外部 XML 檔案讀取資料並進行處理，卻需要一些額外技巧。本文詳細說明如何利用 OPENXML 與 sp_xml_preparedocument 系統預存程序，將實體 XML 檔案載入記憶體並轉換為可查詢的資料列集。

核心架構概念

OPENXML 是一個 Rowset 函數，類似 OPENQUERY 和 OPENROWSET，能對 XML 資料執行 JOIN、INSERT、UPDATE、DELETE 等操作，而無需先匯入資料表。但使用前必須先呼叫 sp_xml_preparedocument 將 XML 文字解析載入記憶體，並取得文件控制代碼 (hdoc)。

系統預存程序參數詳解

sp_xml_preparedocument @hdoc = OUTPUT,
    [, @xmltext = ] 'XML 文字內容',
    [, @xpath_namespaces = '命名空間宣告']

• @hdoc (OUTPUT)：指向記憶體中 XML 文件結構的控制代碼，後續 OPENXML 使用
• @xmltext：實際的 XML 文件內容（字串或文字欄位）
• @xpath_namespaces：XPath 命名空間宣告，URL 需用尖括號括起，如 '<ns1:https://example.com>'

關鍵挑戰：從檔案讀取 XML 內容

Books Online 的範例多假設 XML 內容已在變數中，實務上需從實體檔案讀取。解決方案是利用 xp_cmdshell 執行 type 指令逐行讀取，再組合成大型 VARCHAR 變數。

完整讀取檔案程式碼

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)

CREATE TABLE #tempXML (PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileName = 'C:\Temp\CurrentSettings.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

SELECT @y = COUNT(*) FROM #tempXML
SET @x = 0

WHILE @x < @y
BEGIN
    SET @x = @x + 1
    SELECT @FileContents = @FileContents + ThisLine 
    FROM #tempXML WHERE PK = @x
END

SELECT @FileContents AS FileContents
DROP TABLE #tempXML

完整處理流程範例

-- 1. 讀取檔案到 @FileContents（上述程式碼）

-- 2. 準備 XML 文件
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, @FileContents

-- 3. 使用 OPENXML 查詢
SELECT *
FROM OPENXML(@hdoc, '/root/items/item', 2)
WITH (
    ItemID INT '@id',
    ItemName VARCHAR(100) 'name',
    Price DECIMAL(10,2) 'price'
)

-- 4. 釋放記憶體資源
EXEC sp_xml_removedocument @hdoc

進階應用場景

• 資料匯入：將 XML 直接 INSERT 到資料表，或與現有資料表 JOIN 比對
• 資料同步：比較 XML 與資料庫差異，執行 MERGE 操作
• 動態結構處理：針對結構不固定的 XML，搭配動態 SQL 處理

注意事項

• VARCHAR(8000) 長度限制，大型 XML 需改用 TEXT 或分段處理
• 需啟用 xp_cmdshell，涉及安全性考量
• 記得呼叫 sp_xml_removedocument 釋放記憶體，避免洩漏
• SQL Server 2005+ 建議改用原生 XML 資料型別與 XQuery，功能更強大