使用sp_xml_preparedocument處理XML文檔的方法
發(fā)布日期：2022-01-24 18:34 | 文章來(lái)源：源碼中國(guó)
有時(shí)會(huì)在存儲(chǔ)過(guò)程中處理一些XML格式的數(shù)據(jù)，所以會(huì)用到sp_xml_preparedocument，他可以將XML數(shù)據(jù)進(jìn)行讀取，然后使用 MSXML 分析器 (Msxmlsql.dll) 對(duì)其進(jìn)行分析。我們就可以很容易的在存儲(chǔ)過(guò)程中得到XML中我們想要的數(shù)據(jù)。下面的代碼就是使用sp_xml_preparedocument讀取XML：
復(fù)制代碼 代碼如下:
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，代碼如下：
復(fù)制代碼 代碼如下:
SELECT *
FROM openxml(@hdoc,'/ROOT/Customer',1)
WITH (CustomerID VARCHAR(40),ContactName VARCHAR(40))
OPENXML有三個(gè)參數(shù)：
第一個(gè)是sp_xml_preparedocument讀取是的OUTPUT參數(shù)，在本示例中就是@hdoc；
第二個(gè)是一個(gè)XPath表達(dá)式，用來(lái)獲取指定位置的數(shù)據(jù)；
第三個(gè)是一個(gè)可選項(xiàng)，用來(lái)表示獲取的方式，有0,1,2,8四種取值，詳細(xì)解釋請(qǐng)看
FROM后面的WITH也是可選的，用來(lái)指定獲取哪些數(shù)據(jù)字段，上面代碼中只取了CustomerID和ContactName。上面的查詢結(jié)果如下：
CustomerID ContactName
—————————————- —————————————-
VINET Paul Henriot
LILAS Carlos Gonzlez
如果不指定WITH子句，查詢出來(lái)的是一個(gè)默認(rèn)的表結(jié)構(gòu)，如下：
表格列的解釋說(shuō)明：
| 列名 | 數(shù)據(jù)類型 | 說(shuō)明 |
|---|---|---|
| id | bigint | 文檔節(jié)點(diǎn)的唯一 ID。
根元素的 ID 值為 0。保留負(fù) ID 值。 |
| parentid | bigint | 標(biāo)識(shí)節(jié)點(diǎn)的父節(jié)點(diǎn)。此 ID 標(biāo)識(shí)的父節(jié)點(diǎn)不一定是父元素。具體情況取決于此 ID 所標(biāo)識(shí)節(jié)點(diǎn)的子節(jié)點(diǎn)的節(jié)點(diǎn)類型。例如，如果節(jié)點(diǎn)為文本節(jié)點(diǎn)，則其父節(jié)點(diǎn)可能是一個(gè)屬性節(jié)點(diǎn)。
如果節(jié)點(diǎn)位于 XML 文檔的頂層，則其 ParentID 為 NULL。 |
| 節(jié)點(diǎn)類型 | int | 標(biāo)識(shí)節(jié)點(diǎn)類型，是對(duì)應(yīng)于 XML 對(duì)象模型 (DOM) 節(jié)點(diǎn)類型編號(hào)的一個(gè)整數(shù)。
下列值是可以顯示在此列中以指明節(jié)點(diǎn)類型的值： 1 = 元素節(jié)點(diǎn) 2 = 屬性節(jié)點(diǎn) 3 = 文本節(jié)點(diǎn) 4 = CDATA 部分節(jié)點(diǎn) 5 = 實(shí)體引用節(jié)點(diǎn) 6 = 實(shí)體節(jié)點(diǎn) 7 = 處理指令節(jié)點(diǎn) 8 = 注釋節(jié)點(diǎn) 9 = 文檔節(jié)點(diǎn) 10 = 文檔類型節(jié)點(diǎn) 11 = 文檔片段節(jié)點(diǎn) 12 = 表示法節(jié)點(diǎn) 有關(guān)詳細(xì)信息，請(qǐng)參閱 Microsoft XML (MSXML) SDK 中的“節(jié)點(diǎn)類型屬性”主題。 |
| localname | nvarchar(max) | 提供元素或?qū)傩缘谋镜孛Q。如果 DOM 對(duì)象沒(méi)有名稱，則為 NULL。 |
| prefix | nvarchar(max) | 節(jié)點(diǎn)名稱的命名空間前綴。 |
| namespaceuri | nvarchar(max) | 節(jié)點(diǎn)的命名空間 URI。如果值是 NULL，則命名空間不存在。 |
| datatype | nvarchar(max) | 元素或?qū)傩孕械膶?shí)際數(shù)據(jù)類型，否則是 NULL。數(shù)據(jù)類型是從內(nèi)聯(lián) DTD 中或從內(nèi)聯(lián)架構(gòu)中推斷得出。 |
| prev | bigint | 前一個(gè)同級(jí)元素的 XML ID。如果前面沒(méi)有同級(jí)元素，則為 NULL。 |
| text | ntext | 包含文本形式的屬性值或元素內(nèi)容。如果邊緣表項(xiàng)不需要值則為 NULL。 |
在WITH子句中,我們還可以通過(guò)設(shè)置來(lái)獲取父級(jí)元素的屬性值:
復(fù)制代碼 代碼如下:
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
有時(shí)候XML中的數(shù)據(jù)并不是以屬性的方式存在,而是直接放在節(jié)點(diǎn)中,如下:
復(fù)制代碼 代碼如下:
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>'
此時(shí)要獲Order節(jié)點(diǎn)下的各項(xiàng)的值,可以用下面方法:
復(fù)制代碼 代碼如下:
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é)點(diǎn)的文本的值區(qū)別在于WITH子句的第三個(gè)參數(shù)是否有@符號(hào)
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信