在 SQL Server 環境中,若需要把資料表、檢視或查詢結果轉存為 XML 檔案,可以透過自訂儲存程序搭配 OLE Automation 物件完成。這種做法適合需要將查詢結果提供給其他系統、進行資料交換,或建立可追溯的資料快照。
程式會先建立 ADODB.Recordset 物件,將指定的 SQL 語句或表名開啟為可讀取的結果集,再呼叫 Save 方法輸出 XML。若使用 Windows 驗證,連線字串會採用整合式安全性;若提供使用者名稱與密碼,則會改用指定帳號連線。
呼叫時主要需要三個參數:要查詢的 SQL 或表名、輸出的檔案路徑,以及是否使用特定帳號。若採 Windows 驗證,使用者名稱可留空;若使用 SQL 帳號,則需一併提供密碼。
EXEC p_saveXML
@sql = '地區資料',
@fname = 'C:\地區資料.XML';
如果需要指定帳號,可改為:
EXEC p_saveXML
@sql = '地區資料',
@fname = 'C:\地區資料.XML',
@userid = 'sa',
@password = 'your_password';
在建立 Recordset 前,應先判斷驗證方式並組出合適的連線字串。完成檔案輸出後,務必釋放 COM 物件,避免佔用資源。若過程中發生錯誤,可透過 OLE Automation 的錯誤資訊取得錯誤號、來源與描述,方便後續排查。
CREATE PROC p_saveXML
@sql VARCHAR(8000),
@fname VARCHAR(1000) = 'C:\tmp.xml',
@userid VARCHAR(50) = '',
@password VARCHAR(50) = ''
AS
BEGIN
DECLARE @err INT, @src VARCHAR(255), @desc VARCHAR(255);
DECLARE @obj INT, @constr VARCHAR(1000);
IF ISNULL(@userid, '') = ''
SET @constr = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=' + DB_NAME() + ';Data Source=' + @@SERVERNAME;
ELSE
SET @constr = 'Provider=SQLOLEDB.1;Persist Security Info=True;User ID=' + @userid + ';Password=' + ISNULL(@password, '') + ';Initial Catalog=' + DB_NAME() + ';Data Source=' + @@SERVERNAME;
EXEC @err = sp_oacreate 'adodb.recordset', @obj OUT;
IF @err != 0 GOTO lberr;
EXEC @err = sp_oamethod @obj, 'open', NULL, @sql, @constr;
IF @err != 0 GOTO lberr;
SET @sql = 'del ' + @fname;
EXEC master..xp_cmdshell @sql, NO_OUTPUT;
EXEC @err = sp_oamethod @obj, 'save', NULL, @fname, 1;
IF @err != 0 GOTO lberr;
EXEC sp_oadestroy @obj;
RETURN;
lberr:
EXEC sp_oageterrorinfo 0, @src OUT, @desc OUT;
SELECT CAST(@err AS VARBINARY(4)) AS 錯誤號,
@src AS 錯誤源,
@desc AS 錯誤描述;
END;
此方式依賴伺服器端可執行 OLE Automation 與命令提示字元,且輸出路徑需具備寫入權限。正式環境中建議限制可執行此程序的帳號權限,並避免任意拼接未經驗證的 SQL 字串,以降低安全性風險。