使用sp_xml_preparedocument處理XML文檔的方法

更新時間：2011年08月31日 00:45:00 作者：

有時會在存儲過程中處理一些XML格式的數(shù)據(jù)，所以會用到sp_xml_preparedocument，他可以將XML數(shù)據(jù)進行讀取，然后使用 MSXML 分析器 (Msxmlsql.dll) 對其進行分析。

有時會在存儲過程中處理一些XML格式的數(shù)據(jù)，所以會用到sp_xml_preparedocument，他可以將XML數(shù)據(jù)進行讀取，然后使用 MSXML 分析器 (Msxmlsql.dll) 對其進行分析。我們就可以很容易的在存儲過程中得到XML中我們想要的數(shù)據(jù)。下面的代碼就是使用sp_xml_preparedocument讀取XML：

復制代碼代碼如下:

 
DECLARE @hdoc int 
DECLARE @doc varchar(1000) 
SET @doc =' 
<ROOT> 
<Customer CustomerID="VINET" ContactName="Paul Henriot"> 
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> 
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> 
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> 
</Order> 
</Customer> 
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> 
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> 
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> 
</Order> 
</Customer> 
</ROOT>' 
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc 

上面只是讀取了XML，要想獲取XML數(shù)據(jù)還需要使用OPENXML，代碼如下：

復制代碼代碼如下:

 
SELECT * 
FROM openxml(@hdoc,'/ROOT/Customer',1) 
WITH (CustomerID VARCHAR(40),ContactName VARCHAR(40)) 

OPENXML有三個參數(shù)：
第一個是sp_xml_preparedocument讀取是的OUTPUT參數(shù)，在本示例中就是@hdoc；
第二個是一個XPath表達式，用來獲取指定位置的數(shù)據(jù)；
第三個是一個可選項，用來表示獲取的方式，有0,1,2,8四種取值，詳細解釋請看
FROM后面的WITH也是可選的，用來指定獲取哪些數(shù)據(jù)字段，上面代碼中只取了CustomerID和ContactName。上面的查詢結果如下：
CustomerID ContactName
—————————————- —————————————-
VINET Paul Henriot
LILAS Carlos Gonzlez
如果不指定WITH子句，查詢出來的是一個默認的表結構，如下：

表格列的解釋說明：

列名	數(shù)據(jù)類型	說明
id	bigint	文檔節(jié)點的唯一 ID。根元素的 ID 值為 0。保留負 ID 值。
parentid	bigint	標識節(jié)點的父節(jié)點。此 ID 標識的父節(jié)點不一定是父元素。具體情況取決于此 ID 所標識節(jié)點的子節(jié)點的節(jié)點類型。例如，如果節(jié)點為文本節(jié)點，則其父節(jié)點可能是一個屬性節(jié)點。如果節(jié)點位于 XML 文檔的頂層，則其 ParentID 為 NULL。
節(jié)點類型	int	標識節(jié)點類型，是對應于 XML 對象模型 (DOM) 節(jié)點類型編號的一個整數(shù)。下列值是可以顯示在此列中以指明節(jié)點類型的值： 1 = 元素節(jié)點 2 = 屬性節(jié)點 3 = 文本節(jié)點 4 = CDATA 部分節(jié)點 5 = 實體引用節(jié)點 6 = 實體節(jié)點 7 = 處理指令節(jié)點 8 = 注釋節(jié)點 9 = 文檔節(jié)點 10 = 文檔類型節(jié)點 11 = 文檔片段節(jié)點 12 = 表示法節(jié)點有關詳細信息，請參閱 Microsoft XML (MSXML) SDK 中的“節(jié)點類型屬性”主題。
localname	nvarchar(max)	提供元素或屬性的本地名稱。如果 DOM 對象沒有名稱，則為 NULL。
prefix	nvarchar(max)	節(jié)點名稱的命名空間前綴。
namespaceuri	nvarchar(max)	節(jié)點的命名空間 URI。如果值是 NULL，則命名空間不存在。
datatype	nvarchar(max)	元素或屬性行的實際數(shù)據(jù)類型，否則是 NULL。數(shù)據(jù)類型是從內聯(lián) DTD 中或從內聯(lián)架構中推斷得出。
prev	bigint	前一個同級元素的 XML ID。如果前面沒有同級元素，則為 NULL。
text	ntext	包含文本形式的屬性值或元素內容。如果邊緣表項不需要值則為 NULL。

在WITH子句中,我們還可以通過設置來獲取父級元素的屬性值:

復制代碼代碼如下:

 
DECLARE @hdoc int 
DECLARE @doc varchar(1000) 
SET @doc =' 
<ROOT> 
<Customer CustomerID="VINET" ContactName="Paul Henriot"> 
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" 
OrderDate="1996-07-04T00:00:00"> 
<OrderDetail ProductID="11" Quantity="12"/> 
<OrderDetail ProductID="42" Quantity="10"/> 
</Order> 
</Customer> 
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> 
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
OrderDate="1996-08-16T00:00:00"> 
<OrderDetail ProductID="72" Quantity="3"/> 
</Order> 
</Customer> 
</ROOT>' 

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc 
SELECT * 
FROM OPENXML (@hdoc, '/ROOT/Customer/Order/OrderDetail',2) 
WITH (OrderID int '../@OrderID', 
CustomerID varchar(10) '../@CustomerID', 
OrderDate datetime '../@OrderDate', 
ProdID int '@ProductID', 
Qty int '@Quantity') 

查詢的結果為:
OrderID CustomerID OrderDate ProdID Qty
———– ———- ———————– ———– ———–
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
有時候XML中的數(shù)據(jù)并不是以屬性的方式存在,而是直接放在節(jié)點中,如下:

復制代碼代碼如下:

 
DECLARE @doc varchar(1000) 
SET @doc =' 
<ROOT> 
<Customer CustomerID="VINET" ContactName="Paul Henriot"> 
<Order> 
<OrderID>10248</OrderID> 
<CustomerID>VINET</CustomerID> 
<EmployeeID>5</EmployeeID> 
<OrderDate>1996-07-04T00:00:00</OrderDate> 
</Order> 
</Customer> 
</ROOT>' 

此時要獲Order節(jié)點下的各項的值,可以用下面方法:

復制代碼代碼如下:

 
DECLARE @hdoc int 
DECLARE @doc varchar(1000) 
SET @doc =' 
<ROOT> 
<Customer CustomerID="VINET" ContactName="Paul Henriot"> 
<Order> 
<OrderID>10248</OrderID> 
<CustomerID>VINET</CustomerID> 
<EmployeeID>5</EmployeeID> 
<OrderDate>1996-07-04T00:00:00</OrderDate> 
</Order> 
</Customer> 
</ROOT>' 
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc 
SELECT * 
FROM OPENXML (@hdoc, '/ROOT/Customer/Order',1) 
WITH (OrderID int 'OrderID', 
CustomerID varchar(10) 'CustomerID', 
EmployeeID int 'EmployeeID', 
OrderDate datetime 'OrderDate') 

查詢結果如下:
OrderID CustomerID EmployeeID OrderDate
———– ———- ———– ———————–
10248 VINET 5 1996-07-04 00:00:00.000
可以看出是取屬性值還是取節(jié)點的文本的值區(qū)別在于WITH子句的第三個參數(shù)是否有@符號