Compartilhar via

  • Artigo

Usando FOR XML e OPENXML para publicar e processar dados XML

É possível executar consultas SQL para retornar resultados como XML em vez de conjuntos de linhas padrão. Essas consultas podem ser executadas diretamente ou a partir de procedimentos armazenados e funções definidas pelo usuário. Para recuperar os resultados diretamente, primeiro use a cláusula FOR XML da instrução SELECT. Em seguida, dentro da cláusula FOR XML, especifique um modo de XML: RAW, AUTO, EXPLICIT ou PATH.

Por exemplo, a instrução SELECT a seguir recupera informações das tabelas Sales.Customer e Sales.SalesOrderHeader no banco de dados AdventureWorks2008R2. Essa consulta especifica o modo AUTO na cláusula FOR XML:

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

Embora seja possível usar a cláusula FOR XML para recuperar dados como um documento XML, é possível usar a função Transact-SQL OPENXML para inserir dados representados como um documento XML. O OPENXML é um provedor de conjunto de linhas semelhante a uma tabela ou exibição e fornece um conjunto de linhas sobre documentos XML que estão na memória. O OPENXML permite acesso a dados XML como se eles estivessem em um conjunto de linhas relacionais, fornecendo uma exibição do conjunto de linhas da representação interna de um documento XML. Os registros no conjunto de linhas podem ser armazenados em tabelas do banco de dados. O OPENXML pode ser usado em instruções SELECT e SELECT INTO onde uma exibição ou tabela de origem podem ser especificadas.

O exemplo a seguir mostra o uso do OPENXML em uma instrução INSERT e em uma instrução SELECT. O documento XML de exemplo contém elementos <Customers> e <Orders>.

Primeiro, o procedimento armazenado sp_xml_preparedocument analisa o documento XML. O documento analisado é uma representação em árvore dos nós (elementos, atributos, texto e comentários) no documento XML. Em seguida, o OPENXML faz referência a esse documento XML analisado e fornece uma exibição do conjunto de linhas de todo ou de parte desse documento XML. Uma instrução INSERT que usa OPENXML pode inserir dados desse tipo de conjunto de linhas em uma tabela de banco de dados. Várias chamadas do OPENXML podem ser usadas para fornecer uma exibição do conjunto de linhas de várias partes do documento XML e processá-las, por exemplo, inserindo-as em diferentes tabelas. Esse processo também é referido como XML de fragmentação em tabelas.

No exemplo a seguir, um documento XML é fragmentado de uma maneira que os elementos <Customers> são armazenados na tabela Customers e elementos <Orders> são armazenados na tabela Orders usando duas instruções INSERT. O exemplo também mostra uma instrução SELECT com OPENXML que recupera CustomerID e OrderDate do documento XML. A última etapa do processo é chamar sp_xml_removedocument. Isso é feito para liberar a memória alocada para conter a representação em árvore XML que foi criada durante a fase de análise.

-- 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

A ilustração a seguir mostra a árvore XML analisada do documento XML anterior que foi criado usando sp_xml_preparedocument.

Árvore XML analisada