SQL Server 2000/2005 原生不支援直接讀取外部 XML 檔案。本文實作：xp_cmdshell 呼叫 type 讀檔 → 組合成單一變數 → sp_xml_preparedocument 解析進記憶體 → OPENXML 當作資料表查詢/聯結/異動。
xp_cmdshell 'type C:\path\file.xml' 逐行讀入暫存表。VARCHAR(8000) (或 MAX) 變數。sp_xml_preparedocument @hdoc OUTPUT, @xmltext 取得文件控制代碼。OPENXML(@hdoc, '/root/row', 2) WITH (...) 依 XPath 映射結構描述。sp_xml_removedocument @hdoc 釋放記憶體。DECLARE @FileName varchar(255) = 'C:\Temp\CurrentSettings.xml';
DECLARE @ExecCmd varchar(255) = 'type ' + @FileName;
DECLARE @FileContents varchar(8000) = '';
DECLARE @y INT, @x INT = 0;
CREATE TABLE #tempXML (PK INT IDENTITY(1,1), ThisLine VARCHAR(255));
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 *
FROM OPENXML(@hdoc, '/Settings/Item', 2)
WITH (
ItemID INT '@id',
ItemName VARCHAR(50) 'Name',
ItemValue VARCHAR(100) 'Value'
);
-- 釋放
EXEC sp_xml_removedocument @hdoc;| 函數 | 用途 |
|---|---|
| sp_xml_preparedocument | 剖析 XML 文字至記憶體 DOM，傳回控制代碼 |
| OPENXML | 依 XPath 模式將 XML 節點轉為資料列集，可 JOIN/INSERT/UPDATE/DELETE |
| sp_xml_removedocument | 釋放記憶體，必須成對呼叫 |
OPENXML(@hdoc INT, @rowpattern NVARCHAR, @flags BYTE)
WITH (SchemaDeclaration | TableName)@rowpattern：XPath 路徑，如 '/root/row'。@flags：0=屬性中心、1=屬性優先、2=元素中心 (常用)、8=不複製資料 (效能佳)。WITH：定義結果集欄位對應 XPath，'@attr' 取屬性，'element' 取子元素文字。-- 將 XML 資料寫入資料表
INSERT INTO dbo.ImportTable (ItemID, ItemName, ItemValue)
SELECT ItemID, ItemName, ItemValue
FROM OPENXML(@hdoc, '/Settings/Item', 2)
WITH (ItemID INT '@id', ItemName VARCHAR(50) 'Name', ItemValue VARCHAR(100) 'Value');
-- 更新現有資料
UPDATE t SET t.ItemValue = x.ItemValue
FROM dbo.TargetTable t
JOIN OPENXML(@hdoc, '/Settings/Item', 2)
WITH (ItemID INT '@id', ItemValue VARCHAR(100) 'Value') x
ON t.ItemID = x.ItemID;VARCHAR(8000) 限制 XML 大小；SQL 2005+ 可用 VARCHAR(MAX) 或 XML 型別變數。xp_cmdshell 安全風險，生產環境建議改用 CLR 預存程序 或 SSIS Script Task 讀檔。XML 資料型別欄位/變數配合 .nodes()、.value() 方法，效能與易用性遠優於 OPENXML。此法適用於舊版 SQL Server 或無法使用 CLR/SSIS 環境，新專案優先考慮原生 XML 型別操作。
