OPENXML (SQL Server)

OPENXML, una palabra clave Transact-SQL, proporciona un conjunto de filas sobre documentos XML en memoria similares a una tabla o una vista. OPENXML permite el acceso a datos XML como si fuera un conjunto de filas relacional. Para ello, proporciona una vista de conjunto de filas de la representación interna de un documento XML. Los registros del conjunto de filas se pueden almacenar en tablas de base de datos.

OPENXML se puede usar en instrucciones SELECT y SELECT INTO siempre que los proveedores de conjuntos de datos, una vista u OPENROWSET puedan aparecer como origen. Para obtener información sobre la sintaxis de OPENXML, vea OPENXML (Transact-SQL).

Para escribir consultas en un documento XML mediante OPENXML, primero debe llamar a sp_xml_preparedocument. Esto analiza el documento XML y devuelve un identificador al documento analizado que está listo para su consumo. El documento analizado es una representación de árbol del modelo de objetos de documento (DOM) de varios nodos del documento XML. El identificador de documento se pasa a OPENXML. A continuación, OPENXML proporciona una vista de conjunto de filas del documento, en función de los parámetros que se le pasan.

Nota:

sp_xml_preparedocument usa una versión actualizada de SQL del analizador MSXML, Msxmlsql.dll. Esta versión del analizador MSXML se diseñó para admitir SQL Server y seguir siendo compatible con la versión 2.6 de MSXML.

La representación interna de un documento XML debe quitarse de la memoria llamando al procedimiento almacenado del sistema sp_xml_removedocument para liberar la memoria.

En la ilustración siguiente se muestra el proceso.

Análisis de XML con OPENXML

Tenga en cuenta que para comprender OPENXML, es necesario familiarizarse con las consultas XPath y comprender XML. Para obtener más información sobre la compatibilidad con XPath en SQL Server, vea Uso de consultas XPath en SQLXML 4.0.

Nota:

OpenXML permite que los patrones XPath de fila y columna se parametricen como variables. Esta parametrización podría provocar inyecciones de expresiones XPath, si el programador expone la parametrización a usuarios externos (por ejemplo, si los parámetros se proporcionan a través de un procedimiento almacenado denominado externamente). Para evitar estos posibles problemas de seguridad, se recomienda que los parámetros XPath nunca se expongan a los autores de llamadas externos.

Ejemplo

En el ejemplo siguiente se muestra el uso de OPENXML en una INSERT instrucción y una SELECT instrucción. El documento XML de ejemplo contiene elementos <Customers> y <Orders>.

En primer lugar, el sp_xml_preparedocument procedimiento almacenado analiza el documento XML. El documento analizado es una representación de árbol de los nodos (elementos, atributos, texto y comentarios) del documento XML. OPENXML a continuación, hace referencia a este documento XML analizado y proporciona una vista de conjunto de filas de todos o partes de este documento XML. Una INSERT instrucción que usa OPENXML puede insertar datos de un conjunto de filas de este tipo en una tabla de base de datos. Se pueden usar varias OPENXML llamadas para proporcionar una vista de conjunto de filas de varias partes del documento XML y procesarlas, por ejemplo, insertándolas en tablas diferentes. Este proceso también se conoce como fragmentación de XML en tablas.

En el ejemplo siguiente, un documento XML se fragmenta de forma que <Customers> los elementos se almacenan en la Customers tabla y <Orders> los elementos se almacenan en la Orders tabla mediante dos INSERT instrucciones. El ejemplo muestra también una SELECT instrucción con OPENXML que recupera CustomerID y OrderDate del documento XML. El último paso del proceso es llamar a sp_xml_removedocument. Esto se hace para liberar la memoria asignada para contener la representación de árbol XML interna que se creó durante la fase de análisis.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20));
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers;
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders;
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle;

En la ilustración siguiente se muestra el árbol XML analizado del documento XML anterior que se creó mediante sp_xml_preparedocument.

Árbol XML analizado analizado

Parámetros OPENXML

Los parámetros de OPENXML incluyen lo siguiente:

Un identificador de documento XML (idoc)
Expresión XPath para identificar los nodos que se van a asignar a filas (rowpattern)
Descripción del conjunto de filas que se va a generar
Correspondencia entre las columnas del conjunto de filas y los nodos XML

Controlador de documento XML (idoc)

El identificador de documento es devuelto por el procedimiento almacenado sp_xml_preparedocument.

Expresión XPath para identificar los nodos que se van a procesar (rowpattern)

La expresión XPath especificada como rowpattern identifica un conjunto de nodos en el documento XML. Cada nodo identificado por rowpattern corresponde a una sola fila del conjunto de filas generado por OPENXML.

Los nodos identificados por la expresión XPath pueden ser cualquier nodo XML del documento XML. Si rowpattern identifica un conjunto de elementos en el documento XML, hay una fila en el conjunto de filas para cada nodo de elemento identificado. Por ejemplo, si rowpattern termina en un atributo, se crea una fila para cada nodo de atributo seleccionado por rowpattern.

Descripción del conjunto de filas que se va a generar

OPENXML usa un esquema de conjunto de filas para generar el conjunto de filas resultante. Puede usar las siguientes opciones al especificar un esquema de conjunto de filas.

Uso del formato de tabla Edge

Debe usar el formato de tabla de bordes para especificar un esquema de conjunto de filas. No use la cláusula WITH.

Al hacerlo, OPENXML devuelve un conjunto de filas en el formato de tabla perimetral. Esto se conoce como una tabla perimetral, ya que cada borde del árbol de documentos XML analizado se asigna a una fila del conjunto de filas.

Las tablas perimetrales representan dentro de una sola tabla la estructura de documentos XML específica. Esta estructura incluye los nombres de elemento y atributo, la jerarquía de documentos, los espacios de nombres y las instrucciones de procesamiento. El formato de tabla perimetral permite obtener información adicional que no se expone a través de las metapropiedades. Para obtener más información sobre las metapropiedades, vea Especificar metapropiedades en OPENXML.

La información adicional proporcionada por una tabla perimetral permite almacenar y consultar el tipo de datos de un elemento y atributo, y el tipo de nodo, así como almacenar y consultar información sobre la estructura del documento XML. Con esta información adicional, también puede crear su propio sistema de administración de documentos XML.

Mediante el uso de una tabla perimetral, puede escribir procedimientos almacenados que toman documentos XML como entrada de objetos binarios grandes (BLOB), generar la tabla perimetral y, a continuación, extraer y analizar el documento en un nivel más detallado. Este nivel detallado podría incluir buscar la jerarquía del documento, los nombres de elemento y atributo, los espacios de nombres y las instrucciones de procesamiento.

La tabla perimetral también puede servir como formato de almacenamiento para documentos XML cuando la asignación a otros formatos relacionales no es lógica y un campo ntext no proporciona suficiente información estructural.

En situaciones en las que puede usar un analizador XML para examinar un documento XML, puede usar una tabla perimetral en su lugar para obtener la misma información.

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

Nombre de la columna	Tipo de dato	Descripción
ID	bigint	Es el identificador único del nodo de documento. El elemento raíz tiene un valor de identificador de 0. Los valores de ID negativos están reservados.
parentid	bigint	Identifica el nodo padre. El elemento primario identificado por este identificador no es necesariamente el elemento primario. Sin embargo, esto depende del NodeType del nodo cuyo padre se identifica por este ID. 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.
tipo de nodo	Int	Identifica el tipo de nodo y es un entero que corresponde a la numeración de tipos de nodo del modelo de objetos XML (DOM). A continuación se muestran los valores que pueden aparecer en esta columna para indicar el tipo de nodo: 1 = Nodo de elemento 2 = Nodo de atributo 3 = Nodo de texto 4 = nodo de sección CDATA 5 = Nodo de referencia de entidad 6 = Nodo de entidad 7 = Nodo de instrucción de procesamiento 8 = Nodo de comentario 9 = Nodo de documento 10 = nodo Tipo de documento 11 = Nodo Fragmento de Documento 12 = Nodo de notación Para obtener más información, consulte la sección "Propiedad NodeType" en el SDK de Microsoft XML (MSXML).
localname	nvarchar(max)	Proporciona el nombre local del elemento o atributo. Es NULL si el objeto DOM no tiene un nombre.
prefijo	nvarchar(max)	Es el prefijo de espacio de nombres del nombre del nodo.
namespaceuri	nvarchar(max)	Es el URI del espacio de nombres del nodo. Si el valor es NULL, no hay ningún espacio de nombres presente.
Datatype	nvarchar(max)	Es el tipo de datos real de la fila de elemento o atributo y es NULL en caso contrario. El tipo de datos se deduce del DTD en línea o del esquema en línea.
Prev	bigint	Es el identificador XML del elemento relacionado anterior. Es nulo si no hay ningún hermano anterior directo.
Mensaje de texto	contexto	Contiene el valor del atributo o el contenido del elemento en formato de texto. O es NULL, si la entrada de la tabla perimetral no necesita un valor.

Usar la cláusula WITH para especificar una tabla existente

Puede usar la cláusula WITH para especificar el nombre de una tabla existente. Para ello, solo tiene que especificar un nombre de tabla existente cuyo esquema puede usar OPENXML para generar el conjunto de filas.

Usar la cláusula WITH para especificar un esquema

Puede usar la cláusula WITH para especificar un esquema completo. Al especificar el esquema del conjunto de filas, se especifican los nombres de columna, sus tipos de datos y su asignación al documento XML.

Puede especificar el patrón de columna mediante el parámetro ColPattern en SchemaDeclaration. El patrón de columna especificado se utiliza para mapear una columna de un conjunto de filas al nodo XML identificado por el patrón de fila, y también se emplea para determinar el tipo de mapeo.

Si no se especifica ColPattern para una columna, la columna del conjunto de filas se asigna al nodo XML con el mismo nombre, en función de la asignación especificada por el parámetro flags . Sin embargo, si ColPattern se especifica como parte de la especificación del esquema en la cláusula WITH, reemplaza la asignación especificada en el parámetro flags.

Asignación entre las columnas del conjunto de filas y los nodos XML

En la instrucción OPENXML, opcionalmente puede especificar el tipo de asignación, como centrado en atributos o centrado en elementos, entre las columnas del conjunto de registros y los nodos XML identificados por rowpattern. Esta información se usa en la transformación entre los nodos XML y las columnas del conjunto de filas.

Puede especificar la asignación de dos maneras y también puede especificar ambas:

Mediante el uso del parámetro flags

La asignación especificada por el parámetro flags supone la correspondencia de nombres en la que los nodos XML se asignan a las columnas del conjunto de filas correspondientes con el mismo nombre.
Mediante el uso del parámetro ColPattern

ColPattern, una expresión XPath, se especifica como parte de SchemaDeclaration en la cláusula WITH. La asignación especificada en ColPattern sobrescribe la asignación especificada por el parámetro flags .

ColPattern se puede usar para especificar el tipo de asignación, como centrado en atributos o centrado en elementos, que sobrescribe o mejora la asignación predeterminada indicada por las marcas.

ColPattern se especifica en las siguientes circunstancias:
- El nombre de columna del conjunto de filas es diferente del nombre del elemento o atributo al que está asignado. En este caso, ColPattern se usa para identificar el nombre del atributo y el elemento XML al que se asigna la columna del conjunto de filas.
- Quieres asignar un atributo de metapropiedad a la columna. En este caso, ColPattern se usa para identificar la metapropiedad con la que está relacionada la columna del conjunto de filas. Para obtener más información sobre cómo usar metapropiedades, vea Especificar metapropiedades en OPENXML.

Los parámetros Flags y ColPattern son opcionales. Si no se especifica ninguna asignación, se asume la asignación centrada en atributos. La asignación centrada en atributos es el valor predeterminado del parámetro flags .

Mapa centrado en atributos

Establecer el parámetro flags en OPENXML en 1 (XML_ATTRIBUTES) indica una asignación centrada en atributos. Si las marcas contienen XML_ ATTRIBUTES, el conjunto de filas expuesto proporciona o consume filas donde cada elemento XML se representa como una fila. Los atributos XML se asignan a los atributos definidos en SchemaDeclaration o proporcionados por tablename de la cláusula WITH, en función de la correspondencia de nombres. La correspondencia de nombres significa que los atributos XML de un nombre determinado se almacenan en una columna del conjunto de filas con el mismo nombre.

Si el nombre de columna es diferente del nombre de atributo al que se asigna, debe especificarse ColPattern .

Si el atributo XML tiene un calificador de espacio de nombres, el nombre de columna del conjunto de filas también debe tener el calificador.

Asignación centrada en elementos

Establecer el parámetro flags en OPENXML en 2 (XML_ELEMENTS) especifica la asignación centrada en elementos . Es similar a la asignación centrada en atributos, excepto por las siguientes diferencias:

La correspondencia de nombres en el ejemplo de asignación, la asignación de columnas a un elemento XML con el mismo nombre selecciona los subelementos simples, a menos que se especifique un patrón a nivel de columna. En el proceso de recuperación, si el subelemento es complejo porque contiene subelementos adicionales, la columna se establece en NULL. A continuación, se omiten los valores de atributo de los subelementos.
Para varios subelementos que tienen el mismo nombre, se devuelve el primer nodo.

Véase también

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

Last updated on 2017-06-13

Compartir a través de