关于SqlServer中openxml的使用

最新推荐文章于 2018-09-26 18:55:00 发布

wateruning2

最新推荐文章于 2018-09-26 18:55:00 发布

阅读量2.4k

点赞数 1

CC 4.0 BY-SA版权

文章标签： openxml sqlserver attributes xml border pascal

本文链接：https://blog.youkuaiyun.com/wateruning2/article/details/1837570

本文介绍了在SQLServer中使用openxml处理XML数据的方法，包括定义文档指针、映射标志的含义和作用，以及with子句中的路径描述。通过示例展示了如何从XML节点转换为二维表，强调了映射标志在处理同名ELEMENT和ATTRIBUTE时的重要性，并分享了作者的命名规范建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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 (