如何使用sp

161次閱讀

共計 3683 個字符，預計需要花費 10 分鐘才能閱讀完成。

如何使用 sp_xml_preparedocument 處理 XML 文檔，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

有時會在存儲過程中處理一些 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 子句，查詢出來的是一個默認的表結構，如下：

表格列的解釋說明：

idbigint 文檔節點的唯一 ID。

根元素的 ID 值為 0。保留負 ID 值。

parentidbigint 標識節點的父節點。此 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 中的“節點類型屬性”主題。

localnamenvarchar(max) 提供元素或屬性的本地名稱。如果 DOM 對象沒有名稱，則為 NULL。prefixnvarchar(max) 節點名稱的命名空間前綴。namespaceurinvarchar(max) 節點的命名空間 URI。如果值是 NULL，則命名空間不存在。datatypenvarchar(max) 元素或屬性行的實際數據類型，否則是 NULL。數據類型是從內聯 DTD 中或從內聯架構中推斷得出。prevbigint 前一個同級元素的 XML ID。如果前面沒有同級元素，則為 NULL。textntext 包含文本形式的屬性值或元素內容。如果邊緣表項不需要值則為 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 子句的第三個參數是否有 @符號

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。

正文完