Compartir a través de

  • Artículo

Usar FOR XML y OPENXML para publicar y procesar datos XML

Puede ejecutar consultas SQL para obtener resultados en formato XML en lugar de conjuntos de filas estándar. Estas consultas pueden ejecutarse directamente o desde procedimientos almacenados y funciones definidas por el usuario. Para obtener los resultados directamente, utilice primero la cláusula FOR XML de la instrucción SELECT. A continuación, dentro de la cláusula FOR XML, especifique un modo de XML: RAW, AUTO, EXPLÍCITO o PATH.

Por ejemplo, la siguiente instrucción SELECT recupera información de las tablas Sales.Customer y Sales.SalesOrderHeader de la base de datos AdventureWorks. Esta consulta especifica el modo AUTO en la cláusula FOR XML:

USE AdventureWorks
GO
SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO

Teniendo en cuenta que puede utilizar la cláusula FOR XML para recuperar datos como un documento XML, también puede utilizar la función OPENXML de Transact-SQL para insertar datos representados como un documento XML. OPENXML es un proveedor de conjuntos de filas similar a una tabla o una vista, y proporciona un conjunto de filas relativo a los documentos XML que se encuentran en la memoria. OPENXML permite el acceso a los datos XML como si fueran un conjunto de filas relacional, proporcionando una vista del conjunto de filas de la representación interna de un documento XML. Los registros del conjunto de filas pueden almacenarse en tablas de base de datos. OPENXML puede utilizarse en instrucciones SELECT y SELECT INTO en las que puede especificarse una vista o tabla de origen.

El siguiente ejemplo muestra el uso de OPENXML en las instrucciones INSERT y SELECT. El documento de ejemplo XML contiene los elementos <Customers> y <Orders>.

En primer lugar, el procedimiento almacenado sp_xml_preparedocument analiza el documento XML. El documento analizado es una representación en árbol de los nodos (elementos, atributos, texto y comentarios) del documento XML. OPENXML hace referencia a este documento XML analizado y proporciona una vista de conjunto de filas de todo el documento XML o parte de él. De esta forma, una instrucción INSERT puede insertar datos de dicho conjunto de filas en una tabla de la base de datos mediante OPENXML. Se pueden utilizar varias llamadas OPENXML para proporcionar una vista del conjunto de filas de diferentes partes del documento XML y procesarlas; por ejemplo, insertándolas en tablas diferentes. Este proceso también se conoce como "descomponer XML en tablas".

En el siguiente ejemplo, un documento XML se descompone de forma que los elementos <Customers> se almacenen en la tabla Customers y los elementos <Orders> en la tabla Orders, por medio de dos instrucciones INSERT. El ejemplo también muestra una instrucción SELECT con OPENXML que recupera CustomerID y OrderDate del documento XML. El último paso del proceso consiste en llamar a sp_xml_removedocument. Esto se lleva a cabo para liberar la memoria asignada a la representación interna en árbol XML 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

La ilustración siguiente muestra el árbol XML analizado del documento XML anterior, creado con sp_xml_preparedocument.

Árbol XML analizado