OPENXML (Transact-SQL)

项目
06/22/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例

OPENXML 提供 XML 文档的行集视图。由于 OPENXML 是行集提供程序，因此可在 Transact-SQL 语句中使用， OPENXML 其中行集提供程序（如表、视图或 OPENROWSET 函数）可以显示。

语法

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

注意

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。

参数

idoc

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

rowpattern

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

flag

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

字节值	说明
`0`	默认为 `attribute-centric` 映射。
`1`	使用 `attribute-centric` 映射。可以与 `XML_ELEMENTS` 结合使用。在这种情况下， `attribute-centric` 首先应用映射。接下来， `element-centric` 将针对任何剩余列应用映射。
`2`	使用 `element-centric` 映射。可以与 `XML_ATTRIBUTES` 结合使用。在这种情况下， `element-centric` 首先应用映射。接下来， `attribute-centric` 将针对任何剩余列应用映射。
`8`	可以结合（逻辑 OR）或 `XML_ATTRIBUTES` `XML_ELEMENTS`. 在检索上下文中，此标志指明不得将已用数据复制到溢出属性 `@mp:xmltext`。

SchemaDeclaration

窗体的架构定义：ColNameColType [ ColPattern | MetaProperty ] [ ， ColNameColType [ ColPattern | MetaProperty ] ...

ColName

行集中的列名。
ColType

行集中列的 SQL Server 数据类型。如果列类型不同于属性的基础 xml 数据类型，则将发生类型强制。
ColPattern

可选的通用 XPath 模式，它说明应如何将 XML 节点映射到列。如果未 指定 ColPattern ，则进行默认映射（attribute-centric 或 element-centric 由标志指定的映射）。

指定为 ColPattern 的 XPath 模式用于指定映射（for attribute-centric 和 element-centric mapping）的特殊性质，用于覆盖或增强标志指示的默认映射。

指定为 ColPattern 的通用 XPath 模式也支持元属性。
MetaProperty

提供的 OPENXML元属性之一。如果指定 MetaProperty，则该列包含元属性提供的信息。使用元属性可以提取有关 XML 节点的信息（如相对位置和命名空间信息）。这些元属性提供了比文本表示形式更详细的信息。

TableName

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

注解

该 WITH 子句通过使用 SchemaDeclaration 或指定现有 TableName 来提供行集格式（以及所需的其他映射信息）。如果未指定可选WITH子句，则结果以边缘表格式返回。边界表在单个表中表示细粒度的 XML 文档结构（例如，元素/属性名、文档层次结构、命名空间、PI 等）。

下表介绍了边缘表的结构。

列名称	数据类型	说明
`id`	bigint	文档节点的唯一 ID。根元素具有 ID 值 `0`。保留负的 ID 值。
`parentid`	bigint	标识节点的父节点。此 ID 标识的父元素不一定是父元素，但它取决于 `nodetype` 由此 ID 标识其父级的节点。例如，如果节点是文本节点，则它的父节点可能是属性节点。如果节点位于 XML 文档中的顶层，则其 `ParentID` 为 `NULL`。
`nodetype`	int	标识节点类型。此值是对应于 XML DOM 节点类型编号的整数。节点类型包括： `1` = 元素节点 `2` = 属性节点 `3` = 文本节点
`localname`	nvarchar	给出元素或属性的本地名称。 `NULL` 如果 DOM 对象没有名称，则为。
`prefix`	nvarchar	节点名称的命名空间前缀。
`namespaceuri`	nvarchar	节点的命名空间 URI。如果值为， `NULL`则不存在命名空间。
`data type`	nvarchar	元素或属性行的实际数据类型，否则为 `NULL`。数据类型是从内联 DTD 中或从内联架构中推断得出。
`prev`	bigint	前一个同级元素的 XML ID。 `NULL` 如果没有直接的上一个兄弟姐妹。
`text`	ntext	包含文本形式的属性值或元素内容（或者`NULL`边缘表条目不需要值）。

示例

A. 将基本 SELECT 语句与 OPENXML 配合使用

以下示例使用 sp_xml_preparedocument 创建 XML 图像的内部表示形式。然后对 XML 文档的内部表示形式执行使用 SELECT 行集提供程序的 OPENXML 语句。

flag 值设为 1。此值指示 attribute-centric 映射。因此，XML 属性映射到行集中的列。指定为 /ROOT/Customer 的 rowpattern 标识要处理的 <Customers> 节点。

未指定可选的 ColPattern（列模式）参数，因为列名与 XML 属性名匹配。

OPENXML 行集提供程序创建了一个双列行集（CustomerID 和 ContactName），SELECT 语句从该行集中检索必要的列（在本例中检索所有的列）。

DECLARE @idoc INT, @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>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (
    CustomerID VARCHAR(10),
    ContactName VARCHAR(20)
);

下面是结果集：

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzlez

如果将同一SELECT语句的标志设置为 2，指示element-centric映射，则返回 NULLXML 文档中的两个客户的值CustomerIDContactName，因为没有任何命名CustomerID元素或 ContactName XML 文档中的元素。

下面是结果集：

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

B. 指定 ColPattern 以在列和 XML 属性之间进行映射

下面的查询从 XML 文档返回客户 ID、订单日期、产品 ID 和数量属性。 rowpattern 标识 <OrderDetails> 元素。 ProductID 和 Quantity 是 <OrderDetails> 元素的属性。而 OrderID、CustomerID 和 OrderDate 是父元素 (<Orders>) 的属性。

下面的映射指定了可选 ColPattern：

行集中的 OrderID、CustomerID 和 OrderDate 映射到 XML 文档中的 rowpattern 所标识节点的父节点属性。
行集中的 ProdID 列映射到 ProductID 属性，行集中的 Qty 列映射到 rowpattern 中所标识节点的 Quantity 属性。

element-centric虽然映射是由标志参数指定的，但 ColPattern 中指定的映射将覆盖此映射。

DECLARE @idoc INT, @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">v
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer/Order/OrderDetail', 2) WITH (
    OrderID INT '../@OrderID',
    CustomerID VARCHAR(10) '../@CustomerID',
    OrderDate DATETIME '../@OrderDate',
    ProdID INT '@ProductID',
    Qty INT '@Quantity'
);

下面是结果集。

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

C. 获得边界表格式的结果

以下示例中的示例 XML 文档由 <Customers>、<Orders> 和 <Order_0020_Details> 元素组成。首先调用 sp_xml_preparedocument 以获得文档句柄sp_xml_preparedocument。此文档句柄传递给 OPENXML。

在 OPENXML 语句中，rowpattern (/ROOT/Customers) 标识要处理的 <Customers> 节点。由于未提供子WITH句，OPENXML因此以边缘表格式返回行集。

最后，SELECT 语句检索“边缘”表中的所有列。

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
   </Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
   </Orders>
</Customers>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML(@idoc, '/ROOT/Customers')

EXEC sp_xml_removedocument @idoc;

示例：使用 OPENXML

通过

OPENXML (Transact-SQL)

语法

参数

idoc

rowpattern

flag

SchemaDeclaration

TableName

注解

示例

A. 将基本 SELECT 语句与 OPENXML 配合使用

B. 指定 ColPattern 以在列和 XML 属性之间进行映射

C. 获得边界表格式的结果

反馈

反馈

其他资源

通过

OPENXML (Transact-SQL)

语法

参数

idoc

rowpattern

flag

SchemaDeclaration

TableName

注解

示例

A. 将基本 SELECT 语句与 OPENXML 配合使用

B. 指定 ColPattern 以在列和 XML 属性之间进行映射

C. 获得边界表格式的结果

相关内容

反馈

反馈

其他资源