SQL Server 2000 讓「把資料匯出成 XML」變得相當容易,但反過來想把 XML 資料匯入並加以處理,卻往往令人頭痛。
若翻開官方說明書(Books Online,簡稱 BOL),你會找到 OPENXML 與 OPENROWSET 等相關條目。這些範例多半示範如何處理「已宣告好的 XML 字串變數」,對習慣操作文字的人很友善;可是對真正需要從檔案讀入 XML 再進行處理的開發者而言,卻不夠實用。要解決這個問題,最合適的做法是由外而內、從檔案讀取一直拆解到最終處理。
OPENXML 是一個 rowset 函式(回傳一個 rowset),運作方式與 OPENQUERY、OPENROWSET 這類 rowset 函式相似。透過 OPENXML,你可以在不先匯入資料的情況下直接對 XML 資料做 JOIN,也能搭配 INSERT、SELECT、UPDATE、DELETE 等操作使用。
不過,相較於 OPENQUERY 與 OPENROWSET,使用 OPENXML 前必須多做兩件事,而且需要兩個系統預存程序來支援。
第一個是 sp_xml_preparedocument,它會讀取指定的 XML 文字,把內容解析後載入記憶體。語法如下:
sp_xml_preparedocument @hdoc = OUTPUT, [, @xmltext = ] [, @xpath_namespaces =]
參數說明:
@hdoc:指向某塊記憶體區域的句柄(概念上等同指標),解析後的資料就放在這裡。這是一個輸出參數,程序執行完畢後,變數會帶有指向 XML 內容記憶體位址的句柄。由於後續還會用到,務必保留這個結果。
@xmltext:你真正想處理的 XML 文字。
@xpath_namespaces:XML 資料正常運作所需的命名空間參考;請注意其中出現的任何 URL 都必須用尖括號(<>)包起來。
只要傳入的參數都合法、XML 文件也存在,資料就會被載入記憶體。接著呼叫 sp_xml_preparedocument 並傳入存放 XML 的變數,再執行 OPENXML 即可。語法如下:
OPENXML(idoc int [in], rowpattern nvarchar[in], [flags byte[in]]) [WITH (SchemaDeclaration | TableName)]
由於篇幅有限,這裡無法逐一說明 OPENXML 各參數的細節,建議查閱 BOL 的 Transact-SQL Reference 中 OPENXML 的條目。
現在只剩最後一步:把真正的 XML 檔案匯入 SQL 並加以處理(奇怪的是,所有 BOL 範例都略過了這個關鍵環節)。這裡要特別感謝同事 Billy Pang 的協助,他幫我釐清了做法並提供了程式碼(本文為篇幅需要做了些微修剪)。
核心技巧其實很單純:把檔案一行一行當作文字讀進來,再把所有行串接成一個大的 VARCHAR 變數,最後把這個變數交給前面提到的程式碼處理。
以下程式示範如何讀取檔案並把內容存進變數:
DECLARE @FileName varchar(255) DECLARE @ExecCmd VARCHAR(255) DECLARE @y INT DECLARE @x INT DECLARE @FileContents VARCHAR(8000) CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255)) SET @FileName = 'C:\Temp\CurrentSettings.xml' SET @ExecCmd = 'type ' + @FileName SET @FileContents = '' INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd SELECT @y = count(*) from #tempXML SET @x = 0 WHILE @x <> @y BEGIN SET @x = @x + 1 SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x END SELECT @FileContents as FileContents DROP TABLE #tempXML
執行完畢後,變數 @FileContents 就裝有檔案的完整內容。你只要把它透過 @xmltext 參數傳給 sp_xml_preparedocument,再呼叫 OPENXML 就大功告成。
有了這套做法,你就能對 XML 文件進行各種處理:把 XML 文件與 SQL 資料表直接 JOIN,而不必先匯入資料,再對結果執行 INSERT、UPDATE、DELETE 等操作。