萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2000 讀取 XML 檔案實戰:xp_cmdshell 逐行讀取 + sp_xml_preparedocument + OPENXML

核心難點

SQL 2000 無原生 XML 檔案讀取函數,OPENXML 僅接受記憶體變數,需自行將檔案內容讀入變數。

三步驟核心流程

  1. xp_cmdshell 執行 type 指令逐行讀檔,寫入暫存表;
  2. 迴圈拼接 成單一 VARCHAR(8000) 變數 (大檔案需分批/用 text 類型);
  3. sp_xml_preparedocument 解析 XML 至記憶體 → 回傳 handle;
  4. OPENXML 配合 WITH 子句撈取結構化資料。

完整範例程式碼

DECLARE @FileName VARCHAR(255) = 'C:\Temp\CurrentSettings.xml';
DECLARE @ExecCmd VARCHAR(255), @FileContents VARCHAR(8000), @y INT, @x INT = 0;
CREATE TABLE #tempXML (PK INT IDENTITY(1,1), ThisLine VARCHAR(255));

SET @ExecCmd = 'type ' + @FileName;
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd;
SELECT @y = COUNT(*) FROM #tempXML;

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

-- 解析 XML
DECLARE @hDoc INT;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @FileContents;

-- 查詢範例:假設 XML 結構 
SELECT id, name
FROM OPENXML(@hDoc, '/root/item', 2)
WITH (id INT '@id', name VARCHAR(50) '@name');

-- 釋放記憶體
EXEC sp_xml_removedocument @hDoc;

關鍵提醒

  • VARCHAR(8000) 上限,超大 XML 需改用 TEXT 欄位分段處理或升級 SQL 2005+ 使用 XML 類型;
  • sp_xml_preparedocument 佔用記憶體,**必須配對 sp_xml_removedocument 釋放**,否則記憶體洩漏;
  • OPENXML 的 flags 參數:1=屬性為中心、2=元素為中心、3=混合;
  • xp_cmdshell 需啟用且需 sysadmin 權限,生產環境謹慎使用。

現代替代方案 (SQL 2005+)

  • OPENROWSET(BULK 'file.xml', SINGLE_BLOB) 直接讀檔為 XML 類型;
  • SELECT * FROM OPENXML(...) 仍支援但較少用;
  • SSIS XML 來源/目的地元件,視覺化 ETL。
copyright © 萬盛學電腦網 all rights reserved