SQL Server 使得以XML導出數據變得更加簡單但在SQL Server 中導入XML數據並對其進行處理則有些麻煩
如果你參考Books Online(BOL)你會發現有相關的條目包括OPENXML以及 OPENROWSET所有的這些例子都支持將XML文本作為已經聲明的變量這對於經常處理文本的用戶來說非常方便但對於希望在開發中讀取XML文件並進行相應處理的開發人員來說就不是這樣了處理這樣的問題或許最好從內到外來對其進行分析
OPENXML是一個rowset函數(即返回一個rowset)它的工作方式類似於rowset函數OPENQUERY和 OPENROWSET使用OPENXML可以對XML數據執行JOINs操作而無需首先導入數據你還可以將其同INSERTSELECT 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以獲取更多信息在TransactSQL Reference中查找OPENXML
現在我們已經到達了最後的步驟所有剩下的工作就是導入一個實際的XML文件到SQL並進行處理(很奇快為什麼所有的BOL示例都沒有涉及到這一關鍵的部分)(我必須感謝我的同事Billy Pang所給予的幫助他幫助我解決這個問題並給出了代碼——盡管出於本文需要我對代碼進行了裁減謝謝Billy!) 基本的技巧是將文件逐行按文本讀取然後把所有讀取的行連接為一個大的VARCHAR變量最後將變量傳遞給前面所說的代碼
以下就是讀取文件並將其內容存放到某變量的代碼
DECLARE @FileName varchar()
DECLARE @ExecCmd VARCHAR()
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR()
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY() ThisLine VARCHAR())
SET @FileName = C:\Temp\CurrentSettingsxml
SET @ExecCmd = type + @FileName
SET @FileContents =
INSERT INTO #tempXML EXEC masterdboxp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x =
WHILE @x < > @y
BEGIN
SET @x = @x +
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表格連接在一起而無需導入數據然後對這些數據進行INSERTPDATE和DELETE等任何操作
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22101.html
