OPENXML (Transact-SQL)

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

下面的查询从 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">而 OrderID、CustomerID 和 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">行集中的 OrderID、CustomerID 和 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

&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‘)