快捷導航

使用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。上面的查詢結(jié)果如下：
CustomerID ContactName
—————————————- —————————————-
VINET Paul Henriot
LILAS Carlos Gonzlez
如果不指定WITH子句，查詢出來的是一個默認的表結(jié)構(gòu)，如下：

表格列的解釋說明：

列名	數(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é)點類型，是對應(yīng)于 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é)點有關(guān)詳細信息，請參閱 Microsoft XML (MSXML) SDK 中的“節(jié)點類型屬性”主題。
localname	nvarchar(max)	提供元素或?qū)傩缘谋镜孛Q。如果 DOM 對象沒有名稱，則為 NULL。
prefix	nvarchar(max)	節(jié)點名稱的命名空間前綴。
namespaceuri	nvarchar(max)	節(jié)點的命名空間 URI。如果值是 NULL，則命名空間不存在。
datatype	nvarchar(max)	元素或?qū)傩孕械膶嶋H數(shù)據(jù)類型，否則是 NULL。數(shù)據(jù)類型是從內(nèi)聯(lián) DTD 中或從內(nèi)聯(lián)架構(gòu)中推斷得出。
prev	bigint	前一個同級元素的 XML ID。如果前面沒有同級元素，則為 NULL。
text	ntext	包含文本形式的屬性值或元素內(nèi)容。如果邊緣表項不需要值則為 NULL。

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

復制代碼代碼如下:

 
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') 

查詢的結(jié)果為:
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') 

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