SQL Server 2000 讀取 XML 檔案完整實作：OPENXML + sp_xml_preparedocument

核心難點：BOL 範例多用變數，實務需讀取實體 XML 檔案

SQL Server 2000 提供 OPENXML Rowset 提供者查詢 XML，但需先呼叫 sp_xml_preparedocument 將 XML 文字載入記憶體取得控制代碼 (@hdoc)，再以 OPENXML(@hdoc, ...) 查詢。關鍵在於如何將檔案內容讀入變數。

完整實作步驟

1. 讀取 XML 檔案至變數 (利用 xp_cmdshell 或 OLE 自動化)

DECLARE @FileName VARCHAR(255) = 'C:\data\input.xml';
DECLARE @ExecCmd VARCHAR(255), @y INT, @x INT, @FileContents VARCHAR(8000);

-- 建立暫存表逐行讀取
CREATE TABLE #tempXML (PK INT IDENTITY(1,1), ThisLine VARCHAR(255));

-- 使用 xp_cmdshell type 指令讀取檔案 (需啟用 xp_cmdshell)
SET @ExecCmd = 'type "' + @FileName + '"';
INSERT INTO #tempXML (ThisLine) EXEC xp_cmdshell @ExecCmd;

-- 合併為單一 XML 字串
SELECT @FileContents = COALESCE(@FileContents + '', '') + ThisLine
FROM #tempXML WHERE ThisLine IS NOT NULL AND ThisLine <> 'NULL';

DROP TABLE #tempXML;

替代方案：使用 sp_OACreate 'Scripting.FileSystemObject' 讀取檔案 (參考 p_movefile 範例)，不依賴 xp_cmdshell。

2. 準備 XML 文件並查詢

DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @FileContents;

-- 範例：XML 結構 
SELECT *
FROM OPENXML(@hdoc, '/Orders/Order', 2)
WITH (OrderID INT '@ID', Customer VARCHAR(20) '@Cust', Amount MONEY '@Amt');

-- 釋放記憶體 (重要！否則洩漏)
EXEC sp_xml_removedocument @hdoc;

關鍵參數說明

總結

「讀檔 → 合併字串 → sp_xml_preparedocument → OPENXML 查詢 → sp_xml_removedocument」是 SQL Server 2000 處理外部 XML 檔案的標準流程。注意大型 XML 記憶體壓力，建議分批處理或升級至 SQL Server 2005+ 使用原生 XML 型別與 nodes() 方法。