Ejemplos: usar OPENXMLExamples: Using OPENXML

Los ejemplos de este tema muestran cómo se utiliza OPENXML para crear una vista de conjunto de filas de un documento XML.The examples in this topic show how OPENXML is used to create a rowset view of an XML document. Para obtener más información sobre la sintaxis de OPENXML, vea OPENXML (Transact-SQL).For information about the syntax of OPENXML, see OPENXML (Transact-SQL). Los ejemplos muestran todos los aspectos de OPENXML, pero no especifican metapropiedades en OPENXML.The examples show all aspects of OPENXML, but do not specify metaproperties in OPENXML. Para obtener más información sobre cómo especificar metapropiedades en OPENXML, vea Especificar metapropiedades en OPENXML.For more information about how to specify metaproperties in OPENXML, see Specify Metaproperties in OPENXML.

EjemplosExamples

Al recuperar los datos, se utiliza rowpattern para identificar los nodos del documento XML que determinan las filas.In retrieving the data, rowpattern is used to identify the nodes in the XML document that determine the rows. Además, rowpattern se expresa en el lenguaje del patrón XPath que se utiliza en la implementación XPath de MSXML.Additionally, rowpattern is expressed in the XPath pattern language that is used in the MSXML XPath implementation. Por ejemplo, si el patrón termina en un elemento o atributo, se crea una fila para cada nodo de elemento o atributo que rowpatternseleccione.For example, if the pattern ends in an element or an attribute, a row is created for each element or attribute node that is selected by rowpattern.

El valor flags proporciona una asignación predeterminada.The flags value provides default mapping. Si no se especifica ColPattern en SchemaDeclaration, se asume la asignación especificada en flags .If no ColPattern is specified in the SchemaDeclaration, the mapping specified in flags is assumed. El valor flags se omite si se especifica ColPattern en SchemaDeclaration.The flags value is ignored if ColPattern is specified in SchemaDeclaration. La especificación ColPattern determina la asignación (centrada en atributos o elementos) y también el comportamiento para manejar los datos de desbordamiento y los no usados.The specified ColPattern determines the mapping, attribute-centric or element-centric, and also the behavior in dealing with overflow and unconsumed data.

A.A. Ejecutar una instrucción SELECT simple con OPENXMLExecuting a simple SELECT statement with OPENXML

El documento XML de este ejemplo se compone de los elementos <Customer>, <Order> y <OrderDetail>.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. La instrucción OPENXML recupera información del cliente del documento XML en un conjunto de filas de dos columnas (CustomerID y ContactName).The OPENXML statement retrieves customer information in a two-column rowset, CustomerID and ContactName, from the XML document.

Primero, para obtener un identificador de documento se llama al procedimiento almacenado sp_xml_preparedocument .First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. Este identificador de documento se pasa a OPENXML.This document handle is passed to OPENXML.

La instrucción OPENXML muestra lo siguiente:The OPENXML statement illustrates the following:

  • rowpattern (/ROOT/Customer) identifica los nodos <Customer> que se van a procesar.rowpattern (/ROOT/Customer) identifies the <Customer> nodes to process.

  • El valor del parámetro flags se establece en 1 e indica una asignación centrada en atributos.The flags parameter value is set to 1 and indicates attribute-centric mapping. Como resultado, los atributos XML se asignan a las columnas del conjunto de filas definido en SchemaDeclaration.As a result, the XML attributes map to the columns in the rowset defined in SchemaDeclaration.

  • En SchemaDeclaration, en la cláusula WITH, los valores ColName especificados coinciden con los nombres de atributo XML correspondientes.In SchemaDeclaration, in the WITH clause, the specified ColName values match the corresponding XML attribute names. Por lo tanto, el parámetro ColPattern no se especifica en SchemaDeclaration.Therefore, the ColPattern parameter is not specified in SchemaDeclaration.

    A continuación, la instrucción SELECT recupera todas las columnas del conjunto de filas que proporciona OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @DocHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<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">  
   <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 @DocHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)  
      WITH (CustomerID  varchar(10),  
            ContactName varchar(20))  
EXEC sp_xml_removedocument @DocHandle  

El resultado es el siguiente:This is the result:

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

Si se ejecuta la misma instrucción SELECT con el parámetro flags establecido en 2 para indicar una asignación centrada en elementos, puesto que los elementos <Customer> no disponen de subelementos, los valores de CustomerID y ContactName de ambos clientes se devuelven como NULL.Because the <Customer> elements do not have any subelements, if the same SELECT statement is executed with flags set to 2 to indicate element-centric mapping, the values of CustomerID and ContactName for both the customers are returned as NULL.

@xmlDocument también puede ser de tipo xml o de tipo (n)varchar(max).The @xmlDocument can also be of xml type or of (n)varchar(max) type.

En el documento XML, si <CustomerID> y <ContactName> son subelementos, la asignación centrada en elementos recupera los valores.If <CustomerID> and <ContactName> in the XML document are subelements, the element-centric mapping retrieves the values.

DECLARE @XmlDocumentHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<ROOT>  
<Customer>  
   <CustomerID>VINET</CustomerID>  
   <ContactName>Paul Henriot</ContactName>  
   <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</CustomerID>  
   <ContactName>Carlos Gonzlez</ContactName>  
   <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 @XmlDocumentHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT    *  
FROM      OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)  
           WITH (CustomerID  varchar(10),  
                 ContactName varchar(20))  
EXEC sp_xml_removedocument @XmlDocumentHandle  

El resultado es el siguiente:This is the result:

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

Observe que el identificador de documento devuelto por sp_xml_preparedocument es válido mientras dura el proceso por lotes y no la sesión.Note that the document handle returned by sp_xml_preparedocument is valid for the duration of the batch and not the session.

B.B. Especificar ColPattern para la asignación entre columnas del conjunto de filas y los atributos y elementos XMLSpecifying ColPattern for mapping between rowset columns and the XML attributes and elements

Este ejemplo muestra cómo se especifica el patrón XPath en el parámetro opcional ColPattern para proporcionar una asignación entre columnas del conjunto de filas y los atributos y elementos XML.This example shows how the XPath pattern is specified in the optional ColPattern parameter to provide mapping between rowset columns and the XML attributes and elements.

El documento XML de este ejemplo se compone de los elementos <Customer>, <Order> y <OrderDetail>.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. La instrucción OPENXML recupera información del cliente y del pedido del documento XML en forma de conjunto de filas (CustomerID, OrderDate, ProdID y Qty).The OPENXML statement retrieves customer and order information as a rowset (CustomerID, OrderDate, ProdID, and Qty) from the XML document.

Primero, para obtener un identificador de documento se llama al procedimiento almacenado sp_xml_preparedocument .First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. Este identificador de documento se pasa a OPENXML.This document handle is passed to OPENXML.

La instrucción OPENXML muestra lo siguiente:The OPENXML statement illustrates the following:

  • rowpattern (/ROOT/Customer/Order/OrderDetail) identifica los nodos <OrderDetail> que se van a procesar.rowpattern (/ROOT/Customer/Order/OrderDetail) identifies the <OrderDetail> nodes to process.

    A modo de ilustración, el valor del parámetro flags se establece en 2 e indica una asignación centrada en elementos.For illustration, the flags parameter value is set to 2 and indicates element-centric mapping. Sin embargo, la asignación especificada en ColPattern sobrescribe esta asignación.However, the mapping specified in ColPattern overwrites this mapping. Es decir, el patrón XPath especificado en ColPattern asigna a atributos las columnas del conjunto de filas.That is, the XPath pattern specified in ColPattern maps the columns in the rowset to attributes. El resultado es una asignación centrada en atributos.This results in attribute-centric mapping.

    En SchemaDeclaration, en la cláusula WITH, también se especifica ColPattern con los parámetros ColName y ColType .In SchemaDeclaration, in the WITH clause, ColPattern is also specified with the ColName and ColType parameters. El parámetro opcional ColPattern es el patrón XPath especificado e indica lo siguiente:The optional ColPattern is the XPath pattern specified and indicates the following:

  • Las columnas OrderID, CustomerID y OrderDate del conjunto de filas se asignan a los atributos del elemento primario de los nodos identificados por rowpattern, y rowpattern identifica los nodos <OrderDetail>.The OrderID, CustomerID, and OrderDate columns in the rowset map to the attributes of the parent of the nodes identified by rowpattern, and rowpattern identifies the <OrderDetail> nodes. Por lo tanto, las columnas CustomerID y OrderDate se asignan a los atributos CustomerID y OrderDate del elemento <Order>.Therefore, the CustomerID and OrderDate columns map to CustomerID and OrderDate attributes of the <Order> element.

  • Las columnas ProdID y Qty del conjunto de filas se asignan a los atributos ProductID y Quantity de los nodos identificados en rowpattern.The ProdID and Qty columns in the rowset map to the ProductID and Quantity attributes of the nodes identified in rowpattern.

    A continuación, la instrucción SELECT recupera todas las columnas del conjunto de filas que proporciona OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @XmlDocumentHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<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">  
   <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 @XmlDocumentHandle OUTPUT, @XmlDocument  
-- Execute a SELECT stmt using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)  
WITH (OrderID     int         '../@OrderID',  
      CustomerID  varchar(10) '../@CustomerID',  
      OrderDate   datetime    '../@OrderDate',  
      ProdID      int         '@ProductID',  
      Qty         int         '@Quantity')  
EXEC sp_xml_removedocument @XmlDocumentHandle  

El resultado es el siguiente:This is the result:

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  

El patrón XPath especificado como ColPattern también puede especificarse para asignar los elementos XML a las columnas del conjunto de filas.The XPath pattern specified as ColPattern can also be specified to map the XML elements to the rowset columns. El resultado es una asignación centrada en elementos.This results in element-centric mapping. En el siguiente ejemplo, en el documento XML, <CustomerID> y <OrderDate> son subelementos del elemento <Orders>.In the following example, the XML document <CustomerID> and <OrderDate> are subelements of the <Orders> element. Dado que ColPattern sobrescribe la asignación especificada en el parámetro flags, este no se especifica en OPENXML.Because ColPattern overwrites the mapping specified in the flags parameter, the flags parameter is not specified in OPENXML.

DECLARE @docHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order EmployeeID="5" >  
      <OrderID>10248</OrderID>  
      <CustomerID>VINET</CustomerID>  
      <OrderDate>1996-07-04T00:00:00</OrderDate>  
      <OrderDetail ProductID="11" Quantity="12"/>  
      <OrderDetail ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order  EmployeeID="3" >  
      <OrderID>10283</OrderID>  
      <CustomerID>LILAS</CustomerID>  
      <OrderDate>1996-08-16T00:00:00</OrderDate>  
      <OrderDetail ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>'  
-- Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')  
WITH (CustomerID  varchar(10)   '../CustomerID',  
      OrderDate   datetime      '../OrderDate',  
      ProdID      int           '@ProductID',  
      Qty         int           '@Quantity')  
EXEC sp_xml_removedocument @docHandle  

C.C. Combinar asignaciones centradas en elementos y en atributosCombining attribute-centric and element-centric mapping

En este ejemplo, el parámetro flags se establece en 3 , lo que indica que se aplicarán tanto la asignación centrada en elementos como en atributos.In this example, the flags parameter is set to 3 and indicates that both attribute-centric and element-centric mapping will be applied. En este caso, primero se aplica la asignación centrada en atributos y, a continuación, la centrada en elementos, en todas las columnas donde aún no se ha hecho.In this case, the attribute-centric mapping is applied first, and then element-centric mapping is applied for all the columns not yet dealt with.

DECLARE @docHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument =N'<ROOT>  
<Customer CustomerID="VINET"  >  
     <ContactName>Paul Henriot</ContactName>  
   <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</ContactName>  
   <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 @docHandle OUTPUT, @XmlDocument  

-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer',3)  
      WITH (CustomerID  varchar(10),  
            ContactName varchar(20))  
EXEC sp_xml_removedocument @docHandle  

El resultado es el siguienteThis is the result

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

La asignación centrada en atributos se aplica para CustomerID.The attribute-centric mapping is applied for CustomerID. No existe ningún atributo ContactName en el elemento <Customer>.There is no ContactName attribute in the <Customer> element. Por lo tanto, se aplica la asignación centrada en elementos.Therefore, element-centric mapping is applied.

D.D. Especificar la función text() de XPath como ColPatternSpecifying the text() XPath function as ColPattern

El documento XML de este ejemplo se compone de los elementos <Customer> y <Order>.The XML document in this example is made up of the <Customer> and <Order> elements. La instrucción OPENXML recupera un conjunto de filas que está compuesto por el atributo oid del elemento <Order>, el identificador del elemento primario del nodo identificado por rowpattern y la cadena del valor de hoja del contenido de elemento.The OPENXML statement retrieves a rowset that is made up of the oid attribute from the <Order> element, the ID of the parent of the node identified by rowpattern, and the leaf-value string of the element content.

Primero, para obtener un identificador de documento se llama al procedimiento almacenado sp_xml_preparedocument.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. Este identificador de documento se pasa a OPENXML.This document handle is passed to OPENXML.

La instrucción OPENXML muestra lo siguiente:The OPENXML statement illustrates the following:

  • rowpattern (/root/Customer/Order) identifica los nodos <Order> que se van a procesar.rowpattern (/root/Customer/Order) identifies the <Order> nodes to process.

  • El valor del parámetro flags se establece en 1 e indica una asignación centrada en atributos.The flags parameter value is set to 1 and indicates attribute-centric mapping. Como resultado, los atributos XML se asignan a columnas del conjunto de filas definido en SchemaDeclaration.As a result, the XML attributes map to the rowset columns defined in SchemaDeclaration.

  • En SchemaDeclaration , en la cláusula WITH, los nombres de columna del conjunto de filas oid y amount coinciden con los nombres de atributo XML correspondientes.In SchemaDeclaration in the WITH clause, the oid and amount rowset column names match the corresponding XML attribute names. Por lo tanto, no se especifica el parámetro ColPattern .Therefore, the ColPattern parameter is not specified. En la columna comment del conjunto de filas, la función text()de XPath se especifica como ColPattern.For the comment column in the rowset, the XPath function, text(), is specified as ColPattern. Esto sobrescribe la asignación centrada en atributos especificada en flagsy la columna contiene la cadena del valor de hoja del contenido del elemento.This overwrites the attribute-centric mapping specified in flags, and the column contains the leaf-value string of the element content.

    A continuación, la instrucción SELECT recupera todas las columnas del conjunto de filas que proporciona OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
--sample XML document  
SET @xmlDocument =N'<root>  
  <Customer cid= "C1" name="Janine" city="Issaquah">  
      <Order oid="O1" date="1/20/1996" amount="3.5" />  
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied  
      </Order>  
   </Customer>  
   <Customer cid="C2" name="Ursula" city="Oelde" >  
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue   
             white red">  
            <Urgency>Important</Urgency>  
            Happy Customer.  
      </Order>  
      <Order oid="O4" date="1/20/1996" amount="10000"/>  
   </Customer>  
</root>'  
-- Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument  

-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)  
     WITH (oid     char(5),   
           amount  float,   
           comment ntext 'text()')  
EXEC sp_xml_removedocument @docHandle  

El resultado es el siguiente:This is the result:

oid   amount        comment  
----- -----------   -----------------------------  
O1    3.5           NULL  
O2    13.4          Customer was very satisfied  
O3    100.0         Happy Customer.  
O4    10000.0       NULL  

E.E. Especificar TableName en la cláusula WITHSpecifying TableName in the WITH clause

Este ejemplo especifica TableName en la cláusula WITH, en lugar de SchemaDeclaration.This example specifies TableName in the WITH clause instead of SchemaDeclaration. Resulta útil si dispone de una tabla con la estructura deseada y no se requieren patrones de columnas (parámetro ColPattern ).This is useful if you have a table that has the structure you want and no column patterns, ColPattern parameter, are required.

El documento XML de este ejemplo se compone de los elementos <Customer> y <Order>.The XML document in this example is made up of the <Customer> and <Order> elements. La instrucción OPENXML recupera información del pedido en un conjunto de filas de tres columnas (oid, date y amount) del documento XML.The OPENXML statement retrieves order information in a three-column rowset (oid, date, and amount) from the XML document.

Primero, para obtener un identificador de documento se llama al procedimiento almacenado sp_xml_preparedocument .First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. Este identificador de documento se pasa a OPENXML.This document handle is passed to OPENXML.

La instrucción OPENXML muestra lo siguiente:The OPENXML statement illustrates the following:

  • rowpattern (/root/Customer/Order) identifica los nodos <Order> que se van a procesar.rowpattern (/root/Customer/Order) identifies the <Order> nodes to process.

  • No existe ninguna SchemaDeclaration en la cláusula WITH.There is no SchemaDeclaration in the WITH clause. En vez de eso, se especifica un nombre de tabla.Instead, a table name is specified. Por lo tanto, el esquema de tabla se utiliza como esquema del conjunto de filas.Therefore, the table schema is used as the rowset schema.

  • El valor del parámetro flags se establece en 1 e indica una asignación centrada en atributos.The flags parameter value is set to 1 and indicates attribute-centric mapping. Por lo tanto, los atributos de los elementos, identificados por rowpattern, se asignan a las columnas del conjunto de filas con el mismo nombre.Therefore, attributes of the elements, identified by rowpattern, map to the rowset columns with the same name.

    A continuación, la instrucción SELECT recupera todas las columnas del conjunto de filas que proporciona OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

-- Create a test table. This table schema is used by OPENXML as the  
-- rowset schema.  
CREATE TABLE T1(oid char(5), date datetime, amount float)  
GO  
DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
-- Sample XML document  
SET @xmlDocument =N'<root>  
  <Customer cid= "C1" name="Janine" city="Issaquah">  
      <Order oid="O1" date="1/20/1996" amount="3.5" />  
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very   
             satisfied</Order>  
   </Customer>  
   <Customer cid="C2" name="Ursula" city="Oelde" >  
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue   
             white red">  
          <Urgency>Important</Urgency>  
      </Order>  
      <Order oid="O4" date="1/20/1996" amount="10000"/>  
   </Customer>  
</root>'  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument  

-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)  
     WITH T1  
EXEC sp_xml_removedocument @docHandle  

El resultado es el siguiente:This is the result:

oid   date                        amount  
----- --------------------------- ----------  
O1    1996-01-20 00:00:00.000     3.5  
O2    1997-04-30 00:00:00.000     13.4  
O3    1999-07-14 00:00:00.000     100.0  
O4    1996-01-20 00:00:00.000     10000.0  

F.F. Obtener el resultado en formato de tabla irregularObtaining the result in an edge table format

En este ejemplo, no se especifica la cláusula WITH en la instrucción OPENXML.In this example, the WITH clause is not specified in the OPENXML statement. Como resultado, el conjunto de filas que genera OPENXML tiene un formato de tabla irregular.As a result, the rowset generated by OPENXML has an edge table format. La instrucción SELECT devuelve todas las columnas de la tabla irregular.The SELECT statement returns all the columns in the edge table.

El documento XML del ejemplo se compone de los elementos <Customer>, <Order> y <OrderDetail>.The sample XML document in the example is made up of the <Customer>, <Order>, and <OrderDetail> elements.

Primero, para obtener un identificador de documento se llama al procedimiento almacenado sp_xml_preparedocument.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. Este identificador de documento se pasa a OPENXML.This document handle is passed to OPENXML.

La instrucción OPENXML muestra lo siguiente:The OPENXML statement illustrates the following:

  • rowpattern (/ROOT/Customer) identifica los nodos <Customer> que se van a procesar.rowpattern (/ROOT/Customer) identifies the <Customer> nodes to process.

  • No se proporciona la cláusula WITH.The WITH clause is not provided. Por esta razón, OPENXML devuelve el conjunto de filas en un formato de tabla irregular.Therefore, OPENXML returns the rowset in an edge table format.

    A continuación, la instrucción SELECT recupera todas las columnas de la tabla irregular.The SELECT statement then retrieves all the columns in the edge table.

DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
SET @xmlDocument = N'<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 @docHandle OUTPUT, @xmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer')  

EXEC sp_xml_removedocument @docHandle  

El resultado se devuelve como una tabla irregular.The result is returned as an edge table. Puede escribir consultas con la tabla irregular para obtener información.You can write queries against the edge table to obtain information. Por ejemplo:For example:

  • La siguiente consulta devuelve el número de nodos Customer del documento.The following query returns the number of Customer nodes in the document. Dado que no se especifica la cláusula WITH, OPENXML devuelve una tabla irregular.Because the WITH clause is not specified, OPENXML returns an edge table. La instrucción SELECT consulta la tabla irregular.The SELECT statement queries the edge table.

    SELECT count(*)  
    FROM OPENXML(@docHandle, '/')  
    WHERE localname = 'Customer'  
    
  • La siguiente consulta devuelve los nombres locales de los nodos XML del tipo de elemento.The following query returns the local names of XML nodes of element type.

    SELECT distinct localname   
    FROM OPENXML(@docHandle, '/')   
    WHERE nodetype = 1   
    ORDER BY localname  
    

G.G. Especificar rowpattern con final en atributoSpecifying rowpattern ending with an attribute

El documento XML de este ejemplo se compone de los elementos <Customer>, <Order> y <OrderDetail>.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. La instrucción OPENXML recupera información detallada del pedido en un conjunto de filas de tres columnas (ProductID, Quantity y OrderID) del documento XML.The OPENXML statement retrieves information about the order details in a three-column rowset (ProductID, Quantity, and OrderID) from the XML document.

Primero, para obtener un identificador de documento se llama a sp_xml_preparedocument .First, the sp_xml_preparedocument is called to obtain a document handle. Este identificador de documento se pasa a OPENXML.This document handle is passed to OPENXML.

La instrucción OPENXML muestra lo siguiente:The OPENXML statement illustrates the following:

  • rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) termina con un atributo XML, ProductID.rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) ends with an XML attribute, ProductID. En el conjunto de filas resultante, se crea una fila por cada nodo de atributo seleccionado en el documento XML.In the resulting rowset, a row is created for each attribute node selected in the XML document.

  • En este ejemplo, no se especifica el parámetro flags .In this example, the flags parameter is not specified. En su lugar, las asignaciones se especifican con el parámetro ColPattern .Instead, the mappings are specified by the ColPattern parameter.

    En SchemaDeclaration , en la cláusula WITH, también se especifica ColPattern con los parámetros ColName y ColType .In SchemaDeclaration in the WITH clause, ColPattern is also specified with the ColName and ColType parameters. El parámetro opcional ColPattern es el patrón XPath especificado para indicar lo siguiente:The optional ColPattern is the XPath pattern specified to indicate the following:

  • El patrón XPath (.) especificado como ColPattern en la columna ProdID del conjunto de filas identifica el nodo de contexto (nodo actual).The XPath pattern (.) specified as ColPattern for the ProdID column in the rowset identifies the context node, current node. En cuanto al valor rowpattern especificado, es el atributo ProductID del elemento <OrderDetail>.As per the rowpattern specified, it is the ProductID attribute of the <OrderDetail> element.

  • ColPattern, ../@Quantity, especificado para la columna Qty del conjunto de filas, identifica el atributo Quantity del nodo principal (<OrderDetail>) del nodo de contexto (<ProductID>).The ColPattern, ../@Quantity, specified for the Qty column in the rowset identifies the Quantity attribute of the parent, <OrderDetail>, node of the context node, <ProductID>.

  • Del mismo modo, ColPattern, ../../@OrderID, especificado para la columna OID del conjunto de filas, identifica el atributo OrderID del elemento primario (<Order>) del nodo principal del nodo de contexto.Similarly, the ColPattern, ../../@OrderID, specified for the OID column in the rowset identifies the OrderID attribute of the parent, <Order>, of the parent node of the context node. El nodo principal es <OrderDetail> y el nodo de contexto es <ProductID>.The parent node is <OrderDetail>, and the context node is <ProductID>.

    A continuación, la instrucción SELECT recupera todas las columnas del conjunto de filas que proporciona OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
--Sample XML document  
SET @xmlDocument =N'<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">  
   <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 @docHandle OUTPUT, @xmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')  
       WITH ( ProdID  int '.',  
              Qty     int '../@Quantity',  
              OID     int '../../@OrderID')  
EXEC sp_xml_removedocument @docHandle  

El resultado es el siguiente:This is the result:

ProdID      Qty         OID  
----------- ----------- -------   
11          12          10248  
42          10          10248  
72          3           10283  

H.H. Especificar un documento XML con varios nodos de textoSpecifying an XML document that has multiple text nodes

Si dispone de varios nodos de texto en un documento XML, una instrucción SELECT con ColPattern, text(), solamente devuelve el primer nodo de texto en lugar de todos.If you have multiple text nodes in an XML document, a SELECT statement with a ColPattern, text(), returns only the first text node, instead of all of them. Por ejemplo:For example:

DECLARE @h int  
EXEC sp_xml_preparedocument @h OUTPUT,  
         N'<root xmlns:a="urn:1">  
           <a:Elem abar="asdf">  
             T<a>a</a>U  
           </a:Elem>  
         </root>',  
         '<ns xmlns:b="urn:1" />'  

SELECT * FROM openxml(@h, '/root/b:Elem')  
      WITH (Col1 varchar(20) 'text()')  
EXEC sp_xml_removedocument @h  

La instrucción SELECT devuelve T como resultado (y no TaU).The SELECT statement returns T as the result, and not TaU.

I.I. Especificar el tipo de datos xml en la cláusula WITHSpecifying the xml data type in the WITH clause

En la cláusula WITH, un patrón de columna que se asigna a la columna de tipo de datos xml , tenga o no tenga tipo, debe devolver una secuencia vacía o una secuencia de elementos, instrucciones de procesamiento, nodos de texto y comentarios.In the WITH clause, a column pattern that is mapped to the xml data type column, whether typed or untyped, must return either an empty sequence or a sequence of elements, processing instructions, text nodes, and comments. Los datos se convierten a un tipo de datos xml .The data is cast to an xml data type.

En el siguiente ejemplo, la declaración de esquema de tabla en la cláusula WITH incluye columnas de tipo xml .In the following example, the table schema declaration in the WITH clause includes xml type columns.

DECLARE @h int  
DECLARE @x xml  
set @x = '<Root>  
  <row id="1"><lname>Duffy</lname>  
   <Address>  
            <Street>111 Maple</Street>  
            <City>Seattle</City>  
   </Address>  
  </row>  
  <row id="2"><lname>Wang</lname>  
   <Address>  
            <Street>222 Pine</Street>  
            <City>Bothell</City>  
   </Address>  
  </row>  
</Root>'  

EXEC sp_xml_preparedocument @h output, @x  
SELECT *  
FROM   OPENXML (@h, '/Root/row', 10)  
      WITH (id int '@id',  

            lname    varchar(30),  
            xmlname  xml 'lname',  
            OverFlow xml '@mp:xmltext')  
EXEC sp_xml_removedocument @h  

Específicamente, se está pasando una variable de tipo xml (@x) a la función sp_xml_preparedocument().Specifically, you are passing an xml type variable (@x) to the sp_xml_preparedocument() function.

El resultado es el siguiente:This is the result:

id  lname   xmlname                   OverFlow  
--- ------- ------------------------------ -------------------------------  
1   Duffy   <lname>Duffy</lname>  <row><Address>  
                                   <Street>111 Maple</Street>  
                                   <City>Seattle</City>  
                                  </Address></row>  
2   Wang    <lname>Wang</lname>   <row><Address>  
                                    <Street>222 Pine</Street>  
                                    <City>Bothell</City>  
                                   </Address></row>  

Tenga en cuenta las siguientes observaciones en cuanto al resultado:Note the following from the result:

  • En el caso de la columna lname de tipo varchar(30), su valor se recupera del elemento <lname> correspondiente.For the lname column of varchar(30) type, its value is retrieved from the corresponding <lname> element.

  • En el caso de la columna xmlname de tipo xml , se devuelve el elemento del mismo nombre como su valor.For the xmlname column of xml type, the same name element is returned as its value.

  • La marca se establece en 10.The flag is set to 10. El 10 significa 2 + 8, donde 2 indica asignación centrada en elementos y 8 indica que solo se pueden agregar datos XML no usados a la columna OverFlow definida en la cláusula WITH.The 10 means 2 + 8, where 2 indicates element-centric mapping and 8 indicates that only unconsumed XML data should be added to the OverFlow column defined in the WITH clause. Si la marca se establece en 2, el documento XML completo se copia en la columna OverFlow especificada en la cláusula WITH.If you set the flag to 2, the whole XML document is copied to the OverFlow column that is specified in the WITH clause.

  • Si la columna con la cláusula WITH es una columna XML con tipo y la instancia XML no se ajusta al esquema, se devuelve un error.In case the column in the WITH clause is a typed XML column and the XML instance does not confirm to the schema, an error is returned.

J.J. Recuperar valores individuales de atributos con varios valoresRetrieving individual values from multivalued attributes

Un documento XML puede tener atributos que tienen varios valores.An XML document can have attributes that are multivalued. Por ejemplo, el atributo IDREFS puede tener varios valores.For example, the IDREFS attribute can be multivalued. En un documento XML, los valores de atributo con varios valores se especifican como una cadena con los valores separados por un espacio.In an XML document, the multivalued attribute values are specified as a string, with the values separated by a space. En el siguiente documento XML, el atributo attends del elemento <Student> y el atributo attendedBy de <Clase> tienen varios valores.In the following XML document, the attends attribute of the <Student> element and the attendedBy attribute of <Class> are multivalued. Recuperar valores individuales de un atributo XML con varios valores y almacenar cada valor en una fila separada en la base de datos requiere un trabajo adicional.Retrieving individual values from a multivalued XML attribute and storing each value in a separate row in the database requires additional work. Este ejemplo muestra el proceso.This example shows the process.

Este documento XML de ejemplo consta de los siguientes elementos:This sample XML document is made up of the following elements:

  • <Student><Student>

    Los atributos id (identificador del estudiante), namey attends .The id (student ID), name, and attends attributes. El atributo attends es un atributo con varios valores.The attends attribute is a multivalued attribute.

  • <Class><Class>

    Los atributos id (identificador de la clase), namey attendedBy .The id (class ID), name, and attendedBy attributes. El atributo attendedBy es un atributo con varios valores.The attendedBy attribute is a multivalued attribute.

    El atributo attends de <Student> y el atributo attendedBy de <Class> representan una relación m:n entre las tablas Student y Class.The attends attribute in <Student> and the attendedBy attribute in <Class> represent a m:n relationship between the Student and Class tables. Un estudiante puede tener muchas clases y una clase puede tener varios estudiantes.A student can take many classes and a class can have many students.

    Suponga que desea dividir este documento y guardarlo en la base de datos de la forma siguiente:Assume that you want to shred this document and save it in the database as shown in the following:

  • Guarde los datos de <Student> en la tabla Students.Save the <Student> data in the Students table.

  • Guarde los datos de <Class> en la tabla Courses.Save the <Class> data in the Courses table.

  • Guarde los datos de la relación m:n (entre Student y Class) en la tabla CourseAttendence.Save the m:n relationship data, between Student and Class, in the CourseAttendence table. Para extraer los valores se requiere trabajo adicional.Additional work is required to extract the values. Para recuperar esta información y almacenarla en la tabla, utilice estos procedimientos almacenados:To retrieve this information and store it in the table, use these stored procedures:

    • Insert_Idrefs_ValuesInsert_Idrefs_Values

      Inserta los valores de Id. de curso e Id. de estudiante en la tabla CourseAttendence.Inserts the values of course ID and student ID in the CourseAttendence table.

    • Extract_idrefs_valuesExtract_idrefs_values

      Extrae los identificadores de estudiantes individuales de cada elemento <Course>.Extracts the individual student IDs from each <Course> element. Para recuperar estos valores se utiliza una tabla irregular.An edge table is used to retrieve these values.

    He aquí los pasos:Here are the steps:

-- Create these tables:  
DROP TABLE CourseAttendance  
DROP TABLE Students  
DROP TABLE Courses  
GO  
CREATE TABLE Students(  
                id   varchar(5) primary key,  
                name varchar(30)  
                )  
GO  
CREATE TABLE Courses(  
               id       varchar(5) primary key,  
               name     varchar(30),  
               taughtBy varchar(5)  
)  
GO  
CREATE TABLE CourseAttendance(  
             id         varchar(5) references Courses(id),  
             attendedBy varchar(5) references Students(id),  
             constraint CourseAttendance_PK primary key (id, attendedBy)  
)  
go  
-- Create these stored procedures:  
DROP PROCEDURE f_idrefs  
GO  
CREATE PROCEDURE f_idrefs  
    @t      varchar(500),  
    @idtab  varchar(50),  
    @id     varchar(5)  
AS  
DECLARE @sp int  
DECLARE @att varchar(5)  
SET @sp = 0  
WHILE (LEN(@t) > 0)  
BEGIN   
    SET @sp = CHARINDEX(' ', @t+ ' ')  
    SET @att = LEFT(@t, @sp-1)  
    EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')')  
    SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp)  
END  
Go  

DROP PROCEDURE fill_idrefs  
GO  
CREATE PROCEDURE fill_idrefs   
    @xmldoc     int,  
    @xpath      varchar(100),  
    @from       varchar(50),  
    @to         varchar(50),  
    @idtable    varchar(100)  
AS  
DECLARE @t varchar(500)  
DECLARE @id varchar(5)  

/* Temporary Edge table */  
SELECT *   
INTO #TempEdge   
FROM OPENXML(@xmldoc, @xpath)  

DECLARE fillidrefs_cursor CURSOR FOR  
    SELECT CAST(iv.text AS nvarchar(200)) AS id,  
           CAST(av.text AS nvarchar(4000)) AS refs  
    FROM   #TempEdge c, #TempEdge i,  
           #TempEdge iv, #TempEdge a, #TempEdge av  
    WHERE  c.id = i.parentid  
    AND    UPPER(i.localname) = UPPER(@from)  
    AND    i.id = iv.parentid  
    AND    c.id = a.parentid  
    AND    UPPER(a.localname) = UPPER(@to)  
    AND    a.id = av.parentid  

OPEN fillidrefs_cursor  
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t  
WHILE (@@FETCH_STATUS <> -1)  
BEGIN  
    IF (@@FETCH_STATUS <> -2)  
    BEGIN  
        execute f_idrefs @t, @idtable, @id  
    END  
    FETCH NEXT FROM fillidrefs_cursor INTO @id, @t  
END  
CLOSE fillidrefs_cursor  
DEALLOCATE fillidrefs_cursor  
Go  
-- This is the sample document that is shredded and the data is stored in the preceding tables.  
DECLARE @h int  
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>  
  <Student id = "s1" name = "Student1"  attends = "c1 c3 c6"  />  
  <Student id = "s2" name = "Student2"  attends = "c2 c4" />  
  <Student id = "s3" name = "Student3"  attends = "c2 c4 c6" />  
  <Student id = "s4" name = "Student4"  attends = "c1 c3 c5" />  
  <Student id = "s5" name = "Student5"  attends = "c1 c3 c5 c6" />  
  <Student id = "s6" name = "Student6" />  

  <Class id = "c1" name = "Intro to Programming"   
         attendedBy = "s1 s4 s5" />  
  <Class id = "c2" name = "Databases"   
         attendedBy = "s2 s3" />  
  <Class id = "c3" name = "Operating Systems"   
         attendedBy = "s1 s4 s5" />  
  <Class id = "c4" name = "Networks" attendedBy = "s2 s3" />  
  <Class id = "c5" name = "Algorithms and Graphs"   
         attendedBy =  "s4 s5"/>  
  <Class id = "c6" name = "Power and Pragmatism"   
         attendedBy = "s1 s3 s5" />  
</Data>'  

INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students  

INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses  
/* Using the edge table */  
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance'  

SELECT * FROM Students  
SELECT * FROM Courses  
SELECT * FROM CourseAttendance  

EXECUTE sp_xml_removedocument @h  

K.K. Recuperar datos binarios a partir de datos con codificación base64 en XMLRetrieving binary from base64 encoded data in XML

Con frecuencia, se incluyen datos binarios en XML mediante codificación base64.Binary data is frequently included in XML using base64 encoding. Cuando este XML se divide mediante OPENXML, os datos se reciben con codificación base64.When you shred this XML by using OPENXML, you receive the base64 encoded data. En este ejemplo se muestra cómo se puede convertir en binarios los datos con codificación base64.This example shows how you can convert the base64 encoded data back to binary.

  • Cree una tabla con datos binarios de ejemplo.Create a table with sample binary data.

  • Use una consulta FOR XML y la opción BINARY BASE64 para generar XML que tenga los datos binarios codificados como base64.Use a FOR XML query and the BINARY BASE64 option to construct XML that has the binary data encoded as base64.

  • Divida el XML mediante OPENXML.Shred the XML by using OPENXML. Los datos devueltos por OPENXML tendrán codificación base64.The data returned by OPENXML will be base64 encoded data. A continuación, llame a la función .value para convertirlos de nuevo en binarios.Next, call the .value function to convert it back to binary.

CREATE TABLE T (Col1 int primary key, Col2 varbinary(100))  
go  
-- Insert sample binary data  
INSERT T VALUES(1, 0x1234567890)   
go  
 -- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)  
SELECT * FROM T  
FOR XML AUTO, BINARY BASE64  
go  
-- result  
-- <T Col1="1" Col2="EjRWeJA="/>  

-- Now shredd the sample XML using OPENXML.   
-- Call the .value function to convert   
-- the base64 encoded data returned by OPENXML to binary.  
DECLARE @h int ;  
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>' ;  
SELECT Col1,   
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol   
FROM openxml(@h, '/T')   
WITH (Col1 integer, Col2 varchar(max)) ;  
EXEC sp_xml_removedocument @h ;  
GO  

Éste es el resultado. Los datos binarios devueltos son los datos binarios originales de la tabla T.The binary data returned is the original binary data in table T.

Col1        BinaryCol  
----------- ---------------------  
1           0x1234567890  

Vea tambiénSee Also

sp_xml_preparedocument (Transact-SQL) sp_xml_preparedocument (Transact-SQL)
sp_xml_removedocument (Transact-SQL) sp_xml_removedocument (Transact-SQL)
OPENXML (Transact-SQL) OPENXML (Transact-SQL)
OPENXML (SQL Server) OPENXML (SQL Server)