OPENXML

功能描述

关系运算符OPENXML从XML文档生成一个结果集。

XML处理流程主要分为三个阶段：

准备阶段，使用sp_xml_preparedocument为格式正确的 XML文档准备内部表示形式。
XML解析，使用openxml运算符生成结果集。
回收阶段，使用sp_xml_removedocument释放XML类型数据句柄。

注意事项

该功能仅在数据库兼容模式为SQL Server时支持（即数据库初始化时指定DBCOMPATIBILITY='MSSQL'）。
在调用函数对XML进行处理时，如需将XML文本赋值给@变量使用，则必须设置参数[enable_set_variable_mssql_format]()为on，表示允许数据库在SQL Server兼容模式下使用自定义用户变量的功能。

语法格式

OPENXML ( idoc int [ in ]
    , rowpattern nvarchar [ in ]
    , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

参数说明

idoc

XML 文档的内部表示形式的文档句柄。通过调用 sp_xml_preparedocument 创建 XML 文档的内部表式形式。
rowpattern

XPath 模式，用于标识要作为行处理的节点。节点来自在 idoc 参数中传递句柄的 XML 文档。

flag

指明在 XML 数据和关系行集间使用的映射，以及如何填充溢出列。 flag 为可选输入参数，可以是下列值之一。

取值	描述
0	默认为“以属性为中心”的映射。
1	使用“以属性为中心”的映射。可以与 XML_ELEMENTS 一起使用。在这种情况下，“以属性为中心”映射先应用。接下来，对任何剩余列应用“以元素为中心”映射。
2	使用“以元素为中心”的映射。可以与 XML_ATTRIBUTES 一起使用。在这种情况下，“以属性为中心”映射先应用。接下来，对任何剩余列应用“以元素为中心”映射。

SchemaDeclaration

窗体的架构定义：
```
ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]
```
- ColName
  
  行集中的列名。
- ColType
  
  行集中列的 SQL Server 数据类型。如果列类型不同于属性的基础 xml 数据类型，则将发生类型强制。
- ColPattern
  
  可选的通用 XPath 模式，它说明应如何将 XML 节点映射到列。如果未指定 ColPattern，发生的是默认映射（由 flags 指定的“以属性为中心”或“以元素为中心”映射）。
  
  指定为 ColPattern 的 XPath 模式用于，指定重写或增强由 flags 指明的默认映射的映射的特殊性质（对于“以属性为中心”和“以元素为中心”映射）。
  
  指定为 ColPattern 的通用 XPath 模式也支持元属性。
- MetaProperty
  
  由 OPENXML 提供的元属性之一。如果指定 MetaProperty，则该列包含元属性提供的信息。使用元属性可以提取有关 XML 节点的信息（如相对位置和命名空间信息）。这些元属性提供了比文本表示形式更详细的信息。
TableName

如果具有所需架构的表已经存在且不要求列模式，则为给定的表名（而不是 SchemaDeclaration）。

示例

前置步骤： 设置enable_set_variable_mssql_format参数。

set enable_set_variable_mssql_format=on;

示例： 进行XML解析，从XML文本中解析得到结果集。

1、定义待转换的XML类型文本。

DECLARE @xmlData XML;  --声明@xmlData为XML类型变量
SET @xmlData = '<Employees>  --为上述变量赋值
<Employee>
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Employee>
<Employee>
<FirstName>Jane</FirstName>
<LastName>Smith</LastName>
</Employee>
</Employees>';

2、使用sp_xml_preparedocument为格式正确的 XML文档准备内部表示形式。

DECLARE @idoc int;
set @idoc := sp_xml_preparedocument(@xmlData);

3、使用OPENXML运算符处理XML数据，其中flag取值为2。

SELECT *
FROM OPENXML (@idoc, '/Employees/Employee', 2)
WITH (
"FirstName" nvarchar(50),
"LastName" nvarchar(50)
);

返回结果如下：

 FirstName | LastName
-----------+----------
 John      | Doe
 Jane      | Smith
(2 rows)

4、使用sp_xml_removedocument释放XML类型数据句柄。

select  sp_xml_removedocument(@idoc);

返回结果如下：

 sp_xml_removedocument
-----------------------

(1 row)

参考链接

sp_xml_preparedocument，sp_xml_removedocument

VastbaseG100