OPENXML (Transact-SQL)

OPENXML 通过 XML 文档提供行集视图。 由于 OPENXML 是行集提供程序,因此可在会出现行集提供程序(如表、视图或 OPENROWSET 函数)的 Transact-SQL 语句中使用 OPENXML。

 Transact-SQL 语法约定

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

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

rowpattern

idoc parameter) to be processed as rows.‘ data-guid="a09d47b38a0c6f443711f05b2f933b41">XPath 模式,用来标识要作为行处理的节点(这些节点在 XML 文档中,该文档的句柄由 idoc 参数传递)。

flags

指示应在 XML 数据和关系行集间使用映射以及应如何填充溢出列。 flags is an optional input parameter, and can be one of the following values.‘ data-guid="8f4d38d0aeee4012057bdd37cc56a062">flags 为可选输入参数,可以是下列值之一。

 

字节值

说明

0

默认为“以属性为中心”的映射。

1

使用“以属性为中心”的映射。 可以与 XML_ELEMENTS 一起使用。 这种情况下,首先应用“以属性为中心”的映射,然后对所有未处理的列应用“以元素为中心”的映射。

2

使用“以元素为中心”的映射。 可以与 XML_ATTRIBUTES 一起使用。 这种情况下,首先应用“以属性为中心”的映射,然后对所有未处理的列应用“以元素为中心”的映射。

8

可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用(逻辑或)。 在检索的上下文中,该标志指示不应将已使用的数据复制到溢出属性 @mp:xmltext

SchemaDeclaration

ColNameColType [ColPattern‘ data-guid="63c4ae07aa65a0e39bb8dbd7bc4e0907">窗体的架构定义:ColNameColType [ColPattern |MetaProperty] [,ColNameColType [ColPattern‘ data-guid="4e1dfbbc9fea4d51a7c3cba126d76705"> MetaProperty] [,ColNameColType [ColPattern |MetaProperty]...]‘ data-guid="caea9ffe931f53f8ad91ef3bd7f62dfd"> MetaProperty]...]

ColName

行集中的列名。

ColType

行集中列的 SQL Server 数据类型。 xml data type of the attribute, type coercion occurs.‘ data-guid="8648c38ea1486b2ae47eb2ce87e15301">如果列类型不同于属性的基础 xml 数据类型,则将发生类型强制。

ColPattern

可选的通用 XPath 模式,它说明应如何将 XML 节点映射到列。 ColPattern is not specified, the default mapping (attribute-centric or element-centric mapping as specified by flags) takes place.‘ data-guid="ec129fabbe99f1c2a41b596185e5ddfa">如果没有指定 ColPattern,则发生默认映射(由 flags 指定的“以属性为中心”或“以元素为中心”的映射)。

ColPattern is used to specify the special nature of the mapping (in the case of attribute-centric and element-centric mapping) that overwrites or enhances the default mapping indicated by flags. ‘ data-guid="2a5e75d027a54455e99468395d45186e">指定为 ColPattern 的 XPath 模式用于指定特殊的映射性质(如果发生“以属性为中心”和“以元素为中心”的映射),这些特殊的映射性质可以重写或增强由 flags 所指示的默认映射。

ColPattern also supports the metaproperties.‘ data-guid="7a5a5236823e535c7eff77a7180a54f3">指定为 ColPattern 的通用 XPath 模式也支持元属性。

MetaProperty

由 OPENXML 提供的元属性之一。 MetaProperty is specified, the column contains information provided by the metaproperty.‘ data-guid="ecdc718c877f81631d79d07d4db918c5">如果指定 MetaProperty,则该列包含元属性提供的信息。 使用元属性可以提取有关 XML 节点的信息(如相对位置和命名空间信息)。 它提供了比文本表示形式更详细的信息。

TableName

SchemaDeclaration) if a table with the desired schema already exists and no column patterns are required.‘ data-guid="0e06f4ee1550b88c685f7eb70ce44bd0">如果具有所需架构的表已经存在且不要求列模式,则为给定的表名(而不是 SchemaDeclaration)。

SchemaDeclaration or specifying an existing TableName.‘ data-guid="b0aead493d4cdf9aa958a33e2f50470e">通过使用 SchemaDeclaration 或指定一个现有 TableName,WITH 子句提供一种行集格式(根据需要还可提供其他映射信息)。 如果没有指定可选的 WITH 子句,则以“边缘”表格式返回结果。 边缘表在单个表中表示 XML 文档的细密结构(例如,元素/属性名、文档层次结构、命名空间、处理说明等)。

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

 

列名

数据类型

说明

id

bigint

文档节点的唯一 ID。

根元素具有的 ID 值为 0。 保留负的 ID 值。

parentid

bigint

标识节点的父节点。 此 ID 所标识的父节点不一定是父元素,而是取决于此 ID 所标识节点的子节点的 NodeType。 例如,如果节点是文本节点,则其父节点可能是属性节点。

如果节点位于 XML 文档的顶层,则其 ParentID 为 NULL。

nodetype

int

标识节点类型。 一个对应于 XML DOM 节点类型编号的整数。

节点类型包括:

1 = 元素节点

2 = 属性节点

3 = 文本节点

localname

nvarchar

给出元素或属性的本地名称。 如果 DOM 对象没有名称,则为 NULL。

prefix

nvarchar

节点名称的命名空间前缀。

namespaceuri

nvarchar

节点的命名空间 URI。 如果值为 NULL,则命名空间不存在。

datatype

nvarchar

元素或属性行的实际数据类型,否则为 NULL。 数据类型是从内联 DTD 中或从内联架构中推断得出。

prev

bigint

前一个同级元素的 XML ID。 如果前面没有同级元素,则为 NULL。

text

ntext

包含文本格式的属性值或元素内容(如果“边缘”表项不需要值,则为 NULL)。

A.使用带 OPENXML 的简单 SELECT 语句

sp_xml_preparedocument.‘ data-guid="4313d626be7719f3c9f9801396e799db">以下示例使用 sp_xml_preparedocument 创建 XML 图像的内部表示形式。 SELECT statement that uses an OPENXML rowset provider is then executed against the internal representation of the XML document.‘ data-guid="8524eab84094564cb7471927aab423b6">然后对 XML 文档的内部表示形式执行使用 OPENXML 行集提供程序的 SELECT 语句。

flag value is set to 1.‘ data-guid="0e2650c9c68150d75a8829b2464bc437">flag 值设置为 1。 该值指示“以属性为中心”的映射。 因此,XML 属性映射到行集中的列。 rowpattern specified as /ROOT/Customer identifies the &lt;Customers&gt; nodes to be processed.‘ data-guid="14d960ab8e620901592950d3d0a29b7d">指定为 /ROOT/Customer 的 rowpattern 标识要处理的<Customers> 节点。

ColPattern (column pattern) parameter is not specified because the column name matches the XML attribute names.‘ data-guid="2aa5be19e0131df5c668bddac4121313">没有指定可选的 ColPattern(列模式)参数,因为列名与 XML 属性名称匹配。

OPENXML rowset provider creates a two-column rowset (CustomerID and ContactName) from which the SELECT statement retrieves the necessary columns (in this case, all the columns). ‘ data-guid="13f00af269dce7057b65dfb987aac00f">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 statement is executed with flags set to 2, indicating element-centric mapping, the values of CustomerID and ContactName for both of the customers in the XML document are returned as NULL, because there are not any elements named CustomerID or ContactName in the XML document.‘ data-guid="c0938f514451e47f8cfb006d4b2eccc9">如果将 flags 设置为 2(表示“以元素为中心”的映射)并执行相同的 SELECT 语句,则由于 XML 文档中没有任何元素名为 CustomerID 或ContactName,所以 XML 文档中针对两个客户的 CustomerID 和 ContactName 的值都返回为 NULL。

下面是结果集:

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

B.为列和 XML 属性之间的映射指定 ColPattern

下面的查询从 XML 文档返回客户 ID、订单日期、产品 ID 和数量等属性。 rowpattern identifies the &lt;OrderDetails&gt; elements.‘ data-guid="9a3dbfa28d84bed4f134b85bf1fea562">rowpattern 标识 <OrderDetails> 元素。 ProductID and Quantity are the attributes of the &lt;OrderDetails&gt; element.‘ data-guid="521fa3e30ecf8a537d3742c19a2b6a02">ProductID 和 Quantity 是<OrderDetails> 元素的属性。 OrderID, CustomerID, and OrderDate are the attributes of the parent element (&lt;Orders&gt;).‘ data-guid="648861e403506d5d497d6ad708183451">而 OrderIDCustomerID 和 OrderDate 是父元素 (<Orders>) 的属性。

指定可选的 ColPattern。 这包括以下各项:

  • OrderID, CustomerID, and OrderDate in the rowset map to the attributes of the parent of the nodes identified by rowpattern in the XML document.‘ data-guid="5560d45898484eb92d96468f3ddfa989">行集中的 OrderIDCustomerID 和 OrderDate 映射到 XML 文档中的 rowpattern 所标识节点的父节点属性。

  • ProdID column in the rowset maps to the ProductID attribute, and the Qty column in the rowset maps to the Quantity attribute of the nodes identified in rowpattern.‘ data-guid="bb736cb1d44d8f668ca4143b4f28315a">行集中的 ProdID 列映射到 ProductID 属性,行集中的 Qty 列映射到 rowpattern 中所标识节点的 Quantity 属性。

element-centric mapping is specified by the flags parameter, the mapping specified in ColPattern overwrites this mapping.‘ data-guid="2334327e101808f62db5e0462773dcd9">尽管“以元素为中心”的映射由 flags 参数指定,但 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.获得边缘表格式的结果

&lt;Customers&gt;, &lt;Orders&gt;, and &lt;Order_0020_Details&gt; elements.‘ data-guid="1a9336f0c650d0f58f94dc999566524b">以下示例中的示例 XML 文档由 <Customers><Orders> 和 <Order_0020_Details> 元素组成。 首先调用 sp_xml_preparedocument 以获得文档句柄。 OPENXML.‘ data-guid="4529b644964920c703c2932602a37f0e">此文档句柄传递给 OPENXML

OPENXML statement, the rowpattern (/ROOT/Customers) identifies the &lt;Customers&gt; nodes to process.‘ data-guid="b33e758ff6dc70d73918adbf2135d622">在 OPENXML 语句中,rowpattern (/ROOT/Customers) 标识要处理的 <Customers> 节点。 OPENXML returns the rowset in an edge table format.‘ data-guid="98c39a81702ff4c1912ef96eb817e63a">由于未提供 WITH 子句,因此 OPENXML 以“边缘”表格式返回行集。

SELECT statement retrieves all the columns in the edge table.‘ data-guid="c6d2ce2accd0fb9a81026a00acdd5b64">最后,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‘) 

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。