关于SqlServer中openxml的使用

假定xml节点有如下定义

<? xml version="1.0" ?>

< Result >

< ad id ="3471" >

< Item Time ="953" Frames ="122" TimeStamp ="2007-09-21 14:21:27" AvgArea ="53.577499" />

</ ad >

< ad id ="3472" >

< Item Time ="20281" Frames ="1899" TimeStamp ="2007-09-21 14:20:03" AvgArea ="99.947342" />

</ ad >

< ad id ="3486" >

< Item Time ="12829" Frames ="254" TimeStamp ="2007-09-21 14:20:16" AvgArea ="99.606300" />

</ ad >

< ad id ="3509" >

< Item Time ="10312" Frames ="979" TimeStamp ="2007-09-21 14:21:00" AvgArea ="38.480518" />

< Item Time ="40469" Frames ="632" TimeStamp ="2007-09-21 14:21:07" AvgArea ="31.995926" />

</ ad >

< ad id ="3514" >

< Item Time ="7484" Frames ="692" TimeStamp ="2007-09-21 14:20:24" AvgArea ="0.641638" />

< Item Time ="2484" Frames ="264" TimeStamp ="2007-09-21 14:21:00" AvgArea ="0.846187" />

< Item Time ="40501" Frames ="632" TimeStamp ="2007-09-21 14:21:07" AvgArea ="0.858317" />

</ ad >

< ad id ="3521" >

< Item Time ="6219" Frames ="626" TimeStamp ="2007-09-21 14:21:07" AvgArea ="35.246521" />

</ 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，如果有定义:

this is elementB

</A>

那么想要取到this is elementB这个值的话，脚本需要写成：

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT , '

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

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

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

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

在这个特定的环境下，映射标志的不同导致了结果的不同。在OPENXML函数中分析的xml文本是大小写敏感的，所以如果有良好的命名习惯应该不至于会依靠这个映射标志来确定返回值。我的命名习惯包括：不用单字母作名称，表名全大写，列名用Pascal格式，变量名用camel格式。

以上内容很多是个人理解和实验结果，有错误大家不吝指出。

原文链接：https://blog.csdn.net/wateruning2/article/details/1837570