OPENXML (Transact-SQL)

Article
06/22/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

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

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

idoc

El manipulador del documento de la representación interna de un documento XML. La representación interna de un documento XML se crea mediante una llamada a sp_xml_preparedocument.

rowpattern

El patrón XPath que se usa para identificar los nodos con el fin de que se procesen como filas. Los nodos proceden del documento XML cuyo manipulador se pasa en el parámetro idoc.

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 valores siguientes.

Valor del byte	Descripción
`0`	El valor predeterminado es la asignación `attribute-centric` .
`1`	Use la `attribute-centric` asignación. Se puede combinar con `XML_ELEMENTS`. En este caso, `attribute-centric` primero se aplica la asignación. A continuación, `element-centric` se aplica la asignación para las columnas restantes.
`2`	Use la `element-centric` asignación. Se puede combinar con `XML_ATTRIBUTES`. En este caso, `element-centric` primero se aplica la asignación. A continuación, `attribute-centric` se aplica la asignación para las columnas restantes.
`8`	Se puede combinar (OR lógico) con `XML_ATTRIBUTES` o `XML_ELEMENTS`. Si se trata de una recuperación, esta marca informa de que los datos consumidos no se deberían copiar a la propiedad de desbordamiento `@mp:xmltext`.

SchemaDeclaration

Definición de esquema del formulario: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]

ColName

El nombre de columna en el conjunto de filas.
ColType

El tipo de datos de 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

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 (attribute-centric o element-centric la asignación especificada por marcas).

El patrón XPath especificado como ColPattern se usa para especificar la naturaleza especial de la asignación (para attribute-centric y element-centric la asignación) que sobrescribe o mejora la asignación predeterminada indicada por marcas.

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

Una de las metapropiedades proporcionadas por OPENXML. Si se especifica MetaProperty, la columna contiene información proporcionada por 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. Estas metapropiedades proporcionan más información que la que se puede ver en la representación de texto.

TableName

El nombre de tabla que puede proporcionarse (en lugar de SchemaDeclaration), si ya existe una tabla con el esquema deseado y no se requieren patrones de columna.

Comentarios

La WITH cláusula proporciona un formato de conjunto de filas (y información de asignación adicional según sea 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 perimetral . Las tablas perimetrales representan la estructura refinada del documento XML (por ejemplo, los nombres de elementos o atributos, la jerarquía del documento, los espacios de nombre, las instrucciones de proceso, etc.) en una única tabla.

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

Nombre de la columna	Tipo de datos	Descripción
`id`	bigint	El id. único del nodo del documento. El elemento raíz tiene un valor `0`de identificador . Los valores de identificador negativos están reservados.
`parentid`	bigint	Identifica el elemento primario del nodo. El elemento primario identificado por 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, el elemento primario de él podría ser un nodo de atributo. Si el nodo está en el nivel superior del documento XML, es `ParentID` `NULL`.
`nodetype`	int	Identifica el tipo de nodo. Este valor es un entero que corresponde a la numeración de tipos 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. `NULL` si el objeto DOM no tiene un nombre.
`prefix`	nvarchar	El prefijo del espacio del nombres del nodo.
`namespaceuri`	nvarchar	El URI del espacio de nombres del nodo. Si el valor es `NULL`, no hay ningún espacio de nombres presente.
`data type`	nvarchar	El tipo de datos real de la fila de elemento o atributo; de lo contrario, es `NULL`. El tipo de datos se infiere a partir de las DTD insertadas o del esquema insertado.
`prev`	bigint	El id. XML del elemento anterior del mismo nivel. `NULL` si no hay ningún elemento relacionado anterior directo.
`text`	ntext	Contiene el valor de atributo o el contenido del elemento en formato de texto (o es `NULL` si la entrada de la tabla perimetral no requiere un valor).

Ejemplos

A. Uso de una instrucción SELECT básica 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. Este valor indica la attribute-centric asignación. Por tanto, los atributos XML se asignan a las columnas del conjunto de filas. El valor 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, @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)
);

Este es el conjunto de resultados.

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

Si la misma SELECT instrucción se ejecuta con marcas establecidas en , lo que indica element-centric la asignación2, los valores de CustomerID y ContactName para los clientes del documento XML se devuelven como NULL, porque no hay ningún elemento denominado CustomerID o ContactName en el documento XML.

Este 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. 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>).

El elemento opcional ColPattern se especifica para las siguientes asignaciones:

Los valores 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 element-centric asignación, la asignación especificada en ColPattern sobrescribe esta asignación.

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'
);

Este 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. Obtención del resultado en formato de tabla perimetral

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 valor rowpattern (/ROOT/Customers) identifica los nodos <Customers> que se van a procesar. Dado que no se proporciona la WITH cláusula , OPENXML devuelve el conjunto de filas en un formato de tabla perimetral .

Por último, la instrucción SELECT recupera todas las columnas de la tabla irregular.

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;

Ejemplos: Uso de OPENXML

Comparteix a través de

OPENXML (Transact-SQL)

Sintaxis

Argumentos

idoc

rowpattern

flags

SchemaDeclaration

TableName

Comentarios

Ejemplos

A. Uso de una instrucción SELECT básica con OPENXML

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

C. Obtención del resultado en formato de tabla perimetral

Comentaris

Recursos addicionals

Comparteix a través de

OPENXML (Transact-SQL)

Sintaxis

Argumentos

idoc

rowpattern

flags

SchemaDeclaration

TableName

Comentarios

Ejemplos

A. Uso de una instrucción SELECT básica con OPENXML

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

C. Obtención del resultado en formato de tabla perimetral

Contenido relacionado

Comentaris

Recursos addicionals