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

核心挑戰

SQL Server 2000 雖支援 FOR XML 輸出，但**缺乏直接讀取 XML 檔案的內建函數**。OPENXML 需要已載入記憶體的 XML 文本，而 OPENROWSET 的 Bulk 選項在 2000 版不支援 XML。本文提供「檔案 → 變數 → 記憶體文件 → OPENXML」完整鏈路。

三步驟核心流程

1. 讀取檔案內容到變數：利用 xp_cmdshell 'type C:\Path\File.xml' 逐行讀入暫存表，串接成單一 VARCHAR(8000) / TEXT 變數。
2. 建立記憶體 XML 文件控制代碼：sp_xml_preparedocument @hDoc OUTPUT, @XmlText。
3. OPENXML 解析查詢：SELECT * FROM OPENXML(@hDoc, '/Root/Row', 2) WITH (Col1 INT, Col2 VARCHAR(50) ...)。
4. 釋放記憶體：sp_xml_removedocument @hDoc。

完整可執行範例

DECLARE @FileName VARCHAR(255) = 'C:\Data\Orders.xml';
DECLARE @ExecCmd VARCHAR(255) = 'type ' + @FileName;
DECLARE @XmlText VARCHAR(8000) = '';
DECLARE @hDoc INT;

-- 1. 讀取檔案
CREATE TABLE #tmp (Line VARCHAR(255));
INSERT INTO #tmp EXEC master.dbo.xp_cmdshell @ExecCmd;

DECLARE @Line VARCHAR(255), @RowCount INT = 0;
SELECT @RowCount = COUNT(*) FROM #tmp WHERE Line IS NOT NULL AND Line <> 'NULL';

DECLARE cur CURSOR FAST_FORWARD FOR
    SELECT Line FROM #tmp WHERE Line IS NOT NULL AND Line <> 'NULL';
OPEN cur;
FETCH NEXT FROM cur INTO @Line;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @XmlText = @XmlText + @Line;
    FETCH NEXT FROM cur INTO @Line;
END
CLOSE cur; DEALLOCATE cur;
DROP TABLE #tmp;

-- 2. 準備文件
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XmlText;

-- 3. 查詢解析 (假設 XML 結構: ...)
SELECT OrderID, CustID
FROM OPENXML(@hDoc, '/Orders/Order', 1)  -- flags=1: attribute-centric
WITH (
    OrderID INT '@OrderID',
    CustID  VARCHAR(10) '@CustID'
);

-- 4. 釋放
EXEC sp_xml_removedocument @hDoc;

關鍵技術細節

大檔案 / 效能優化策略

• 分批處理：XML 拆分為多個小檔，迴圈處理。
• 避免 VARCHAR(8000) 截斷：使用 TEXT / NTEXT 變數或直接將 xp_cmdshell 結果 INSERT INTO #tmp EXEC ... 後用 TEXTPTR + UPDATETEXT 組裝（複雜）。
• 替代方案：
  • **SQLCLR** (2005+)：File.ReadAllText + XmlDocument.LoadXml 高效安全。
  • **SSIS XML 來源**：視覺化、支援 XSD、錯誤導向。
  • **BULK INSERT + OPENROWSET(BULK...)** (2005+)：原生讀取檔案，無需 xp_cmdshell。

命名空間處理

DECLARE @Ns VARCHAR(255) = '';
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XmlText, @Ns;
SELECT * FROM OPENXML(@hDoc, '/ns1:Root/ns1:Row', 2) WITH (...);

結語

SQL Server 2000 讀取 XML 檔案需組合 xp_cmdshell、sp_xml_preparedocument、OPENXML 三大組件。雖可行但維護成本高、安全性弱、記憶體壓力大。**現代專案強烈建議升級至 SQL 2005+ 使用原生 XML 型別、SQLCLR 或 SSIS**，徹底解決檔案讀取與解析難題。