您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 使用 OPENXML 解析 XML 檔案並匯入資料表完整範例

編輯：關於SqlServer

SQL Server 2005 引入原生 XML 資料型別，配合 OPENROWSET(BULK...) 讀取檔案內容，再透過 sp_xml_preparedocument + OPENXML 將 XML 節點轉為關聯式資料列集，實現無需外部 ETL 工具即可將 XML 檔案匯入資料表。本文提供完整可執行腳本，含測試 XML 內容與預期結果。

一、完整匯入腳本


DECLARE @idoc INT;
DECLARE @doc XML;

-- 1. 讀取 XML 檔案內容到 XML 變數 (SINGLE_BLOB 以二進位讀取)
SELECT @doc = BulkColumn
FROM OPENROWSET(
    BULK 'D:/test.xml',
    SINGLE_BLOB
) AS x;

-- 2. 建立 XML 文件控制代碼 (內部剖析樹)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- 3. 使用 OPENXML 將指定 XPath 節點映射為資料列集，寫入暫存表
SELECT * INTO #temp
FROM OPENXML(@idoc, '/Root/Item', 2) -- 2 = 屬性中心映射 (Attribute-centric), 1 = 元素中心
WITH (
    [ID] VARCHAR(10),
    [Name] VARCHAR(10),
    [Caption] VARCHAR(10)
);

-- 4. 查詢結果
SELECT * FROM #temp;

-- 5. 清理資源
DROP TABLE #temp;
EXEC sp_xml_removedocument @idoc; -- 釋放記憶體，極重要！

二、測試用 XML 檔案內容 (D:/test.xml)




  
    1
    jinjazz
    剪刀
  
  
    2
    zswang
    伴水

三、查詢結果


ID  Name      Caption
--- --------- -------
1   jinjazz   剪刀
2   zswang    伴水

四、關鍵技術點解析

元件	說明
`OPENROWSET(BULK..., SINGLE_BLOB)`	將檔案完整讀入單一二進位值 (varbinary(max))，再隱性轉為 XML 型別。需啟用 `Ad Hoc Distributed Queries` 伺服器選項。
`sp_xml_preparedocument`	將 XML 文件剖析為內部 DOM 樹，傳回控制代碼 (`@idoc`)，供 `OPENXML` 重複使用。必須配對呼叫 `sp_xml_removedocument` 釋放記憶體，否則會造成記憶體洩漏。
`OPENXML(@idoc, xpath, flags)`	`@idoc`：文件控制代碼 `xpath`：目標節點路徑 (如 `/Root/Item`) `flags`：映射模式 0 = 預設 (元素中心) 1 = 屬性中心 2 = 元素中心 + 屬性中心 (常用) 8 = 不複製資料 (唯讀, 效能佳)
`WITH (Schema)`	定義輸出資料行名稱、型別、對應 XPath (預設同名元素/屬性)。可用 `ColName XML '@attr'` 對應屬性。

五、現代替代方案 (SQL Server 2005+)

原生 XML 型別提供 .nodes()、.value()、.query() 方法，**無需 OPENXML 即可直接查詢**，語法更直觀、效能更佳、無記憶體洩漏風險：


-- 現代寫法：直接對 XML 變數查詢
DECLARE @doc XML = (SELECT BulkColumn FROM OPENROWSET(BULK 'D:/test.xml', SINGLE_BLOB) AS x);

SELECT
    T.c.value('(ID)[1]', 'VARCHAR(10)') AS ID,
    T.c.value('(Name)[1]', 'VARCHAR(10)') AS Name,
    T.c.value('(Caption)[1]', 'VARCHAR(10)') AS Caption
FROM @doc.nodes('/Root/Item') AS T(c);

建議：新專案優先使用 .nodes() 方法；舊系統維護或需相容 SQL 2000 才使用 OPENXML。

六、常見錯誤與排查

Ad Hoc Distributed Queries 未啟用：EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
檔案路徑錯誤/權限不足：SQL Server 服務帳戶需具備檔案系統讀取權限，路徑為伺服器端本機路徑 (非用戶端)。
編碼不符：XML 宣告 encoding (UTF-8) 需與實際檔案編碼一致，否則剖析失敗。
忘記 sp_xml_removedocument：長時間運行導致記憶體耗盡，需在 TRY...CATCH 的 FINALLY 區塊確保釋放。