程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 關於SqlServer中openxml的使用

關於SqlServer中openxml的使用

編輯：關於SqlServer

假定XML節點有如下定義

<?XML version="1.0" ?>

</ad>

</ad>

</ad>

</ad>

</ad>

</ad>

</Result>

上述的XML文件節點表達的邏輯結構為：根節點Result下存在若干個ad節點，每個ad節點下含有若干個Item節點。

現有需求將以上節點平展成一張二維表：

ADID varchar(10), --ad.id,

ShowTimes int,-- Item.Time,

Frames int,-- Item.Frames,

StatTime datetime,-- Item.TimeStamp,

AvgArea decimal(20,4)-- Item.AvgArea

)

腳本組織如下：

-- Create an internal representation of the XML document.

DECLARE @idoc int

EXEC sp_XML_preparedocument @idoc OUTPUT, '

</ad>

</ad>

</ad>

</ad>

</ad>

</ad>

</Result>'

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/Result/ad/Item', 1)

WITH (ADID varchar(10) '../@id',

ShowTimes int '@Time',

Frames int,

StatTime datetime '@TimeStamp',

AvgArea decimal(20,4)

)

--free the document

EXEC sp_XML_removedocument @idoc

使用OPENXML()需要一個准備動作：定義一個文檔指針@idoc，類型為int（sp_xml_preparedocument）。使用完後還需要把它釋放掉（sp_XML_removedocument）。

OPENXML()本身返回一個行集，並且需要3個參數和一組列映射定義。
3個參數分別是：預先准備好的文檔指針@idoc，搜索路徑起點，映射標志。

映射標志有如下定義：

標識字節值描述 XML_NOCOPY 0 默認為以特性為中心的映射。 XML_ATTRIBUTES 1 使用以特性為中心的映射。在某些情況下，可以將它與 XML_ELEMENTS 組合使用。使用時首先應用以特性為中心的映射，然後對於所有仍未處理的列應用以元素為中心的映射。 XML_ELEMENTS 2 使用以元素為中心的映射。在某些情況下，可以將它與 XML_ATTRIBUTES 組合使用。使用時先應用以特性為中心的映射，然後對於所有仍未處理的列應用以元素為中心的映射。 XML_NOCOPY 8 可與 XML_ATTRIBUTES 或 XML_ELEMENTS 組合使用（邏輯 OR）。在檢索的上下文中，該標志指明不應將已消耗的數據復制到溢出屬性 @mp:XMLtext。

這些標志可以組合使用，比如選擇3表示 XML_ATTRIBUTES + XML_ATTRIBUTES，如果with子句中的路徑描述是完整的，那麼這個標志實際上並沒有作用。

with子句中的路徑(ColPattern)描述：

With (ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...] )

'../@id'就是一個ColPattern描述，..表示取當前深度(Result/ad/Item)的上一級，也就是Result/ad；/@id表示取這個節點的名為id的ATTRIBUTE值。'@'是專用於ATTRIBUTE的前綴符號。

除了取ATTRIBUTE值以外，還可以取ELEMENT值。ColPattern中的無前綴標識表示ELEMENT，如果有定義:

<A ID="1">
 this is elementB
 </A>

那麼想要取到this is elementB這個值的話，腳本需要寫成：

DECLARE @idoc int

 EXEC sp_XML_preparedocument @idoc OUTPUT, '
 <A ID="1">
 this is elementB
 </A>'

 SELECT *
 FROM OPENXML (@idoc, 'A/B', 0)
 WITH (B varchar(20) '../B')

 EXEC sp_XML_removedocument @idoc

ColPattern中還有一種標識：'@mp:???'，用於返回XMLDOM中的一些元屬性值(Meta Property)，比如@mp:id表示當前元素在DOM中的唯一標識；@mp:parentid表示當前元素的父節點標識。比如

DECLARE @idoc int

 EXEC sp_XML_preparedocument @idoc OUTPUT, '
 <A ID="1">
 this is elementB
 </A>'

 SELECT *
 FROM OPENXML (@idoc, 'A/B', 0)
 WITH (
 curid varchar(20) '@mp:id',
   parid varchar(20) '@mp:parentid'
 )

 EXEC sp_XML_removedocument @idoc

可被選擇的Meta Property有： @mp:id Provides system-generated, document-wide identifier of the DOM node. An XML ID of 0 indicates that the element is a root element and its @mp:parentid is NULL. @mp:parentid Same as @mp:id, only for the parent. @mp:localname Provides the non-fully qualified name of the node. It is used with prefix and namespace URI to name element or attribute nodes. @mp:parentlocalname Same as @mp:localname, only for the parent. @mp:namespaceuri Provides the namespace URI of the current element. If the value of this attribute is NULL, no namespace is present. @mp:parentnamespaceuri Same as @mp:namespaceuri, only for the parent. @mp:prefix The namespace prefix of the current element name. @mp:prev Stores the @mp:id of the previous sibling relative to a node. Using this, we can tell something about the ordering of the elements at the current level of the hIErarchy. @mp:xmltext This Meta property is used for processing purposes, and contains the actual XML text for the current element as used in the overflow handling of OPENXML.

這些元屬性值實際上都屬於一個“元屬性表”，官方名稱：邊緣表。邊緣表可以通過省略WITH子句的方式獲取：

DECLARE @idoc int

 EXEC sp_XML_preparedocument @idoc OUTPUT, '
 <A ID="1">
 this is elementB
 </A>'

 SELECT *
 FROM OPENXML (@idoc, 'A/B', 0)

 EXEC sp_XML_removedocument @idoc

各列的含義與上表一一對應。

映射標志有什麼用？

前文提到OPENXML()的3個參數分別是：預先准備好的文檔指針@idoc，搜索路徑，映射標志。...如果with子句中的路徑描述是完整的，那麼這個(映射)標志實際上並沒有作用。

with子句中的路徑描述ColPattern是允許不提供的（而且With子句中允許提供一個表作為它的列定義，此時ColPattern是不存在的），這個時候就會根據ColName的名稱去自動尋找合適的元素，如果這時候存在同名的兩個ELEMENT和ATTRIBUTE的話，就需要一個優先級仲裁，這就是映射標志的作用。

DECLARE @idoc int

 EXEC sp_XML_preparedocument @idoc OUTPUT, '
 <A B="1">
 this is elementB
 </A>'

 SELECT *
 FROM OPENXML (@idoc, 'A', 1)
 WITH (
 B varchar(20)
 )

 SELECT *
 FROM OPENXML (@idoc, 'A', 2)
 WITH (
 B varchar(20)
 )

 EXEC sp_XML_removedocument @idoc