有時會在存儲過程中處理一些XML格式的數據，所以會用到sp_xml_preparedocument，他可以將XML數據進行讀取，然後使用 MSXML 分析器 (Msxmlsql.dll) 對其進行分析。我們就可以很容易的在存儲過程中得到XML中我們想要的數據。下面的代碼就是使用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數據還需要使用OPENXML，代碼如下：
復制代碼 代碼如下:
SELECT *
FROM openxml(@hdoc,'/ROOT/Customer',1)
WITH (CustomerID VARCHAR(40),ContactName VARCHAR(40))
OPENXML有三個參數：
第一個是sp_xml_preparedocument讀取是的OUTPUT參數，在本示例中就是@hdoc；
第二個是一個XPath表達式，用來獲取指定位置的數據；
第三個是一個可選項，用來表示獲取的方式，有0,1,2,8四種取值，詳細解釋請看
FROM後面的WITH也是可選的，用來指定獲取哪些數據字段，上面代碼中只取了CustomerID和ContactName。上面的查詢結果如下：
CustomerID ContactName
—————————————- —————————————-
VINET Paul Henriot
LILAS Carlos Gonzlez
如果不指定WITH子句，查詢出來的是一個默認的表結構，如下：
表格列的解釋說明：
根元素的 ID 值為 0。保留負 ID 值。
parentid bigint 標識節點的父節點。此 ID 標識的父節點不一定是父元素。具體情況取決於此 ID 所標識節點的子節點的節點類型。例如，如果節點為文本節點，則其父節點可能是一個屬性節點。如果節點位於 XML 文檔的頂層，則其 ParentID 為 NULL。
節點類型 int 標識節點類型，是對應於 XML 對象模型 (DOM) 節點類型編號的一個整數。下列值是可以顯示在此列中以指明節點類型的值：
1 = 元素節點
2 = 屬性節點
3 = 文本節點
4 = CDATA 部分節點
5 = 實體引用節點
6 = 實體節點
7 = 處理指令節點
8 = 注釋節點
9 = 文檔節點
10 = 文檔類型節點
11 = 文檔片段節點
12 = 表示法節點
有關詳細信息，請參閱 Microsoft XML (MSXML) SDK 中的“節點類型屬性”主題。
localname nvarchar(max) 提供元素或屬性的本地名稱。如果 DOM 對象沒有名稱，則為 NULL。 prefix nvarchar(max) 節點名稱的命名空間前綴。 namespaceuri nvarchar(max) 節點的命名空間 URI。如果值是 NULL，則命名空間不存在。 datatype nvarchar(max) 元素或屬性行的實際數據類型，否則是 NULL。數據類型是從內聯 DTD 中或從內聯架構中推斷得出。 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中的數據並不是以屬性的方式存在,而是直接放在節點中,如下:
復制代碼 代碼如下:
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節點下的各項的值,可以用下面方法:
復制代碼 代碼如下:
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
可以看出是取屬性值還是取節點的文本的值區別在於WITH子句的第三個參數是否有@符號
