SQL Server中讀取XML檔案的簡單做法

SQL Server 2000使得以XML匯出資料變得更加簡單，但在SQL Server 2000中匯入XML資料並對其進行處理則有些麻煩。本文介紹在SQL Server中讀取XML檔案的簡單做法。[@more@]

SQL Server 2000使得以XML匯出資料變得更加簡單，但在SQL Server 2000中匯入XML資料並對其進行處理則有些麻煩。
　　如果你參考Books Online（BOL），你會發現有相關的條目，包括OPENXML以及 OPENROWSET。所有的這些例子都支援將XML文字作為已經宣告的變數，這對於經常處理文字的使用者來說非常方便，但對於希望在開發中讀取XML檔案並進行相應處理的開發人員來說就不是這樣了。處理這樣的問題，或許最好從內到外來對其進行分析。
　　OPENXML是一個rowset函式（即返回一個rowset），它的工作方式類似於rowset函式OPENQUERY和OPENROWSET。使用OPENXML可以對XML資料執行JOINs操作而無需首先匯入資料。你還可以將其同INSERT、SELECT、UPDATE以及DELETE等操作聯合使用。 然而，要使用OPENXML，你必須執行兩項OPENQUERY和OPENROWSET並不需要的任務。這兩項任務需要兩個系統儲存程式。 第一個是sp_xml_preparedocument，它將讀取特定的XML文字並將其內容提取到記憶體中。其語法如下：
　　sp_xml_preparedocument @hdoc = OUTPUT,
　　[, @xmltext = ]
　　[, @xpath_namespaces =
　　具體引數如下： @hdoc：指向某記憶體區域的控制程式碼（從作用上看等同於一個指標），相關資料存放在這裡。注意這是一個輸出變數，當該程式執行後，該變數將包含指向XML檔案內容在記憶體地址的控制程式碼。由於你需要在隨後使用此結果，因此要確保對其進行儲存； @xmltext：實際上你所希望處理的XML文字； @xml_namespaces：為了正常操作你的XML資料所需要的任何名字空間索引（namespace references）。注意在這裡出現的任何URL都需要用尖括號（<>）括起來； 假設所傳遞的這些引數都有效，並且XML文件存在，那麼你的XML資料就會被存放到記憶體中去。現在你就可以呼叫sp_xml_preparedocument，傳遞存放有XML檔案的變數，然後執行OPENXML。語法如下：
　　OPENXML(idocint [in],rowpatternnvarchar[in],[flagsbyte[in]])
　　[WITH (SchemaDeclaration 　 TableName)]
　　注意：在本文中沒有足夠的文字來描述OPENXML所接收的引數。請參閱BOL以獲取更多資訊。在Transact-SQL Reference中查詢OPENXML。
　　現在我們已經到達了最後的步驟。所有剩下的工作就是匯入一個實際的XML檔案到SQL並進行處理（很奇快為什麼所有的BOL示例都沒有涉及到這一關鍵的部分）。（我必須感謝我的同事Billy Pang所給予的幫助。他幫助我解決這個問題，並給出了程式碼——儘管出於本文需要我對程式碼進行了裁減。謝謝Billy！） 基本的技巧是，將檔案逐行按文字讀取。然後把所有讀取的行連線為一個大的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:TempCurrentSettings.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表格連線在一起而無需匯入資料，然後對這些資料進行INSERT、PDATE和DELETE等任何操作。