OPENXML (Transact-SQL)

  • Artículo

OPENXML proporciona una vista de un conjunto de filas en un documento XML. Puesto que OPENXML es un proveedor de conjuntos de filas, puede utilizarse en las instrucciones de Transact-SQL en las que pueden aparecer proveedores de conjuntos de filas como una tabla, vista o la función OPENROWSET.

Icono de vínculo a temasConvenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

  • idoc
    Es el identificador del documento de la representación interna de un documento XML. La representación interna de un documento XML se crea llamando a sp_xml_preparedocument.
  • rowpattern
    Es el patrón XPath utilizado para identificar los nodos (en el documento XML cuyo identificador se pasa en el parámetro idoc) que se van a procesar como filas.

  • flags
    Indica la asignación que debe utilizarse entre los datos XML y el conjunto de filas relacional, y cómo debe llenarse la columna de desbordamiento; flags es un parámetro de entrada opcional, y puede tomar uno de los siguientes valores.

    Valor del byte Descripción

    0

    Establece como valor predeterminado la asignación attribute-centric.

    1

    Usa la asignación attribute-centric. Se puede combinar con XML_ELEMENTS. En ese caso, primero se aplica la asignación attribute-centric y, a continuación, la asignación element-centric en todas las columnas que todavía no se han visto afectadas.

    2

    Usa la asignación element-centric. Se puede combinar con XML_ATTRIBUTES. En ese caso, primero se aplica la asignación attribute-centric y, a continuación, la asignación element-centric en todas las columnas que todavía no se han visto afectadas.

    8

    Puede combinarse (OR lógico) con XML_ATTRIBUTES o XML_ELEMENTS. Si se trata de una recuperación, este indicador informa de que los datos consumidos no se deberían copiar a la propiedad de desbordamiento @mp:xmltext.

  • SchemaDeclaration
    Es la definición de esquema de la forma: ColNameColType [ColPattern | MetaProperty] [**,**ColNameColType [ColPattern | MetaProperty]...]

    • ColName
      Es el nombre de columna en el conjunto de filas.
    • ColType
      Es el tipo de datos SQL Server de la columna en el conjunto de filas. Si los tipos de columna son distintos del tipo de datos xml subyacente del atributo, se producirá una conversión de tipos.

    • ColPattern
      Es un parámetro opcional, un patrón XPath general que describe la forma de asignar los nodos XML a las columnas. Si no se especifica ColPattern, se realiza la asignación predeterminada (asignación (attribute-centric o element-centric especificada en los valores flags).

      El patrón XPath especificado como ColPattern se utiliza para especificar la naturaleza especial de la asignación (en caso de una asignación attribute-centric y element-centric ) que sobrescribe o mejora la asignación predeterminada especificada por los indicadores flags.

      El patrón XPath general especificado como ColPattern también admite las metapropiedades.

    • MetaProperty
      Es una de las metapropiedades que proporciona OPENXML. Si se especifica MetaProperty, la columna contiene información que proporciona la metapropiedad. Las metapropiedades permiten extraer información (como información sobre el espacio de nombres y la posición relativa) acerca de nodos XML. Esto proporciona más información que la que se puede ver en la representación de texto.
  • TableName
    Es el nombre de tabla que puede proporcionarse (en lugar de SchemaDeclaration) si ya existe una tabla con el esquema deseado y no se requiere patrones de columna.

Notas

La cláusula WITH proporciona un formato de conjunto de filas (e información de asignación adicional si es necesario) mediante SchemaDeclaration o especificando un TableName existente. Si no se especifica la cláusula opcional WITH, los resultados se devuelven en un formato de tabla irregular. Las tablas irregulares representan la estructura refinada del documento XML (por ejemplo, los nombres de elementos o atributos, la jerarquía del documento, los espacios de nombres, las instrucciones de proceso, etc.) en una única tabla.

En la siguiente tabla se describe la estructura de la tabla irregular.

Nombre de columna Tipo de datos Descripción

id

bigint

Es el id. único del nodo del documento.

El elemento raíz tiene como identificador el valor 0. Se reservan los valores negativos de identificadores.

parentid

bigint

Identifica el elemento primario del nodo. El nodo primario que identifica este identificador no es necesariamente el elemento primario, pero depende del NodeType del nodo cuyo elemento primario se identifica mediante este identificador. Por ejemplo, si el nodo es un nodo de texto, su elemento primario puede ser un nodo de atributo.

Si el nodo está en el nivel superior del documento XML, su ParentID es NULL.

nodetype

int

Identifica el tipo de nodo. Es un entero que se corresponde con la numeración del tipo de nodo XML DOM.

Los tipos de nodo son:

1 = Nodo de elemento

2 = Nodo de atributo

3 = Nodo de texto

localname

nvarchar

Proporciona el nombre local del elemento o atributo. Es NULL si el objeto DOM no tiene nombre.

prefijo

nvarchar

Es el prefijo del espacio del nombre del nodo.

namespaceuri

nvarchar

Es el URI del espacio de nombres del nodo. Si el valor es NULL, no hay ningún espacio de nombres.

datatype

nvarchar

Es el tipo de datos reales de la fila del elemento o atributo y, en caso contrario, es NULL. El tipo de datos se infiere a partir de las DTD en línea o del esquema en línea.

prev

bigint

Es el id. XML del anterior elemento del mismo nivel. Es NULL si no existe ningún elemento previo directo del mismo nivel.

text

ntext

Contiene el valor de atributo o el contenido de elemento en forma de texto (o es NULL si la entrada de la tabla irregular no se requiere un valor).

Ejemplos

A. Usar una instrucción SELECT simple con OPENXML

En el siguiente ejemplo se crea una representación interna de la imagen XML utilizando sp_xml_preparedocument. A continuación se ejecuta una instrucción SELECT que usa un proveedor del conjunto de filas OPENXML contra la representación interna del documento XML.

El valor de flag se establece en 1. Esto indica la asignación attribute-centric. Por tanto, los atributos XML se asignan a las columnas del conjunto de filas. El rowpattern especificado como /ROOT/Customer identifica los nodos <Customers> que se van a procesar.

El parámetro opcional ColPattern (patrón de columna) no se especifica porque el nombre de columna coincide con los nombres de atributos XML.

El proveedor del conjunto de filas OPENXML crea un conjunto de filas de 2 columnas (CustomerID y ContactName) desde el que la instrucción SELECT recupera las columnas necesarias (en este caso, todas las columnas).

DECLARE @idoc int
DECLARE @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))

Éste es el conjunto de resultados.

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

Si se ejecuta la misma instrucción SELECT con el parámetro flags establecido en 2, lo que indica una asignación element-centric, ya que los elementos <Customers> no tienen ningún subelemento, los valores de CustomerID y ContactName para los dos clientes en el documento XML se devuelven como NULL.

Éste es el conjunto de resultados.

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

B. Especificar ColPattern para la asignación entre columnas y los atributos XML

En la siguiente consulta se devuelven los atributos de CustomerID, OrderDate, ProductID y Quantity del documento XML. El rowpattern identifica los elementos <OrderDetails>. ProductID y Quantity son los atributos del elemento <OrderDetails>. No obstante, OrderID, CustomerID y OrderDate son los atributos del elemento primario (<Orders>).

Se especifica el parámetro opcional colpattern. Indica lo siguiente:

  • Las columnas OrderID, CustomerID y OrderDate del conjunto de filas se asignan a los atributos del elemento primario de los nodos identificados por rowpattern en el documento XML.
  • La columna ProdID del conjunto de filas se asigna al atributo ProductID, y la columna Qty del conjunto de filas se asigna al atributo Quantity de los nodos identificados en rowpattern.

Aunque el parámetro flags especifica la asignación element-centric, la asignación especificada en ColPattern sobrescribe la anterior.

DECLARE @idoc int
DECLARE @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">
   <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')

Éste es el conjunto de resultados.

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. Obtener el resultado en formato de tabla irregular

El documento XML del ejemplo siguiente está formado por los elementos <Customers>, <Orders> y <Order_0020_Details>. Primero se llama a sp_xml_preparedocument para obtener un identificador de documentos. Este identificador de documentos se pasa a OPENXML.

En la instrucción OPENXML, el rowpattern (/ROOT/Customers) identifica los nodos <Customers> que se van a procesar. Puesto que no se proporciona la cláusula WITH, OPENXML devuelve el conjunto de filas en un formato de tabla irregular.

Finalmente, la instrucción SELECT recupera todas las columnas de la tabla irregular.

DECLARE @idoc int
DECLARE @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

Vea también

Otros recursos

Usar OPENXML

Ayuda e información

Obtener ayuda sobre SQL Server 2005