Este artigo faz parte da série de artigos sobre a cláusula de consulta FOR XML, suportada pelo SQL Server 2005 e usada para retornar os dados do banco de dados em formato XML. Este artigo descreve o uso do modo EXPLICIT.

O que é o modo de consulta EXPLICIT

Algumas vezes precisamos trocar documentos XML entre sistemas, principalmente os que estão fora do nosso domínio, que não podem ser gerados com os modos de consulta RAW e AUTO, pois estes não oferecem maior controle sobre o formato do XML gerado pela consulta. Entretanto, o modo EXPLICIT provê maior flexibilidade em gerar o XML que requisitamos.

Para que o modo EXPLICIT gere o documento XML, os dados retornados pela consulta precisam estar em um formato específico. Isso requer que as consultas SELECT sejam escritas de uma forma que retornem o resultado num formato específico, conhecido como tabela universal, e assim poder gerar o XML desejado.

Primeiro, o modo EXPLICIT requer que a consulta gere duas colunas de meta dados:

  • A primeira coluna tem o nome de Tag, é um tipo de dado inteiro, e armazena o número da tag do elemento atual. Essa coluna identifica o nível na árvore hierárquica em que o elemento está localizado.
  • A segunda coluna tem o nome de Parent, é um tipo de dado inteiro, e identifica o número da Tag do elemento pai. Os elementos do primeiro nível da hierárquica têm o valor de Parent igual a NULL. Dessa forma, as colunas Tag e Parent são meta dados que determinam a estrutura hierárquica dos dados.

O restante das colunas são os dados que serão apresentados no documento, e correspondem a uma coluna física no banco de dados. O nome de cada coluna especifica como os dados serão apresentados: como um elemento, um atributo ou um subelemento. As colunas de dados têm um nome formado por quatro partes, de acordo com a seguinte estrutura:

ElementName!TagNumber!AttributeName!Directive

A tabela a seguir descreve cada parte do nome de coluna:

Parte do nome Descrição
ElementName O nome do elemento que contém os dados dessa linha.
TagNumber Um número único que identifica a tag. O mesmo ElementName precisa ser usado com determinado TagNumber.
AttributeName O nome do atributo ou do subelemento que representa o dado nessa coluna (opcional).
Directive Instrução de formatação adicional (opcional).

Para entender como o resultado retornado pela consulta é processado para gerar o XML requisitado, assumimos que a consulta retorne a seguinte tabela:

Tag Parent Customer!1!cid Customer!1!name Order!2!id Order!2!date OrderDetail!3!id OrderDetail!3!pid
1 NULL C1 “Janine” NULL NULL NULL NULL
2 1 C1 NULL 01 20/01/1996 NULL NULL
3 2 C1 NULL 01 NULL OD1 P1
3 2 C1 NULL 01 NULL OD2 P2
2 1 C1 NULL 02 29/03/1997 NULL NULL

As linhas são processadas em ordem e a hierarquia do XML é determinada conforme as regras abaixo:

A primeira linha especifica os valores de Tag = 1 e Parent = NULL, portanto, o elemento correspondente é adicionado no nível pai do documento XML.

<Customer cid="C1" name="Janine">

A segunda linha especifica os valores de Tag = 2 e Parent = 1, conseqüentemente, o elemento <Order> é adicionado como um filho do elemento <Customer>.

<Customer cid="C1" name="Janine">
   <Order id="01" date="20/01/1996">

As duas próximas linhas especificam os valores de Tag = 3 e Parent = 2, portanto, os dois elementos <OrderDetail> são adicionados como filhos do elemento <Order>.

<Customer cid="C1" name="Janine">
   <Order id="01" date="20/01/1996">
      <OrderDetail id="OD1" pid="P1"/>
      <OrderDetail id="OD2" pid="P2"/> 

A última linha especifica o valor de Tag = 2 e Parent = 1, conseqüentemente, outro elemento filho <Order> é adicionado ao elemento pai <Customer>:

<Customer cid="C1" name="Janine">
   <Order id="01" date="20/01/1996">
      <OrderDetail id="OD1" pid="P1"/>
      <OrderDetail id="OD2" pid="P2"/>
   </Order>
   <Order id="02" date="29/03/1997">
</Customer>

Depois de você ter definido a estrutura da tabela necessária para gerar o documento XML, você pode construir a consulta Transact SQL necessária para gerar a tabela usando o nome das colunas especificadas. Você pode informar explicitamente os valores para as colunas Tag e Parent, como mostra o exemplo abaixo:

SELECT 1 AS Tag,
        NULL AS Parent,
        SalesOrderID AS [Order!1!Id],
        CONVERT(VARCHAR(10), OrderDate, 103) AS [Order!1!Date],
        CustomerID AS [Order!1!Customer]
 FROM Sales.SalesOrderHeader
 FOR XML EXPLICIT

Esta consulta produz um fragmento XML no seguinte formato:

<Order Id="43659" Date="01/07/2001" Customer="676" />
<Order Id="43660" Date="01/07/2001" Customer="117" />
<Order Id="43661" Date="01/07/2001" Customer="442" />
<Order Id="43662" Date="01/07/2001" Customer="227" />
<Order Id="43663" Date="01/07/2001" Customer="510" />

Se quisermos que os dados OrderDate e CustomerID sejam apresentados como subelementos do elemento Order, basta adicionar a diretiva “Element” ao nome dos campos, como mostra o exemplo abaixo:

SELECT 1 AS Tag,
        NULL AS Parent,
        SalesOrderID AS [Order!1!Id],
        CONVERT(VARCHAR(10), OrderDate, 103) AS [Order!1!Date!Element],
        CustomerID AS [Order!1!Customer!Element]
 FROM Sales.SalesOrderHeader
 FOR XML EXPLICIT

O resultado é um fragmento XML como mostrado no exemplo abaixo:

<Order Id="43659">
  <Date>01/07/2001</Date>
  <Customer>676</Customer>
</Order>
<Order Id="43660">
  <Date>01/07/2001</Date>
  <Customer>117</Customer>
</Order>

O exemplo a seguir retorna os dados de pedido (OrderHeader) e itens de pedido (OrderDetail). Como existem dois níveis de hierarquia, usamos duas consultas e aplicamos um UNION ALL. Precisamos ordenar o resultado por OrderId e ItemId para que os elementos sejam exibidos na ordem correta.

SELECT 1 AS Tag,
        NULL AS Parent,
        H.SalesOrderID AS [Order!1!Id],
        CONVERT(VARCHAR(10), H.OrderDate, 103) AS [Order!1!Date],
        H.CustomerID AS [Order!1!Customer],
        NULL AS [Item!2!Id],
        NULL AS [Item!2!Product],
        NULL AS [Item!2!Qty],
        NULL AS [Item!2!Total]
 FROM Sales.SalesOrderHeader H
 WHERE H.SalesOrderID IN (43659,43660,43661)
 UNION ALL
 SELECT 2 AS Tag,
        1 AS Parent,
        H.SalesOrderID,
        CONVERT(VARCHAR(10), H.OrderDate, 103),
        H.CustomerID,
        D.SalesOrderID,
        D.ProductID,
        D. OrderQty,
        D.LineTotal
 FROM Sales.SalesOrderHeader H, Sales.SalesOrderDetail D
 WHERE H.SalesOrderID = D.SalesOrderID
 AND H.SalesOrderID IN (43659,43660,43661)
 ORDER BY [Order!1!Id], [Item!2!Id]
 FOR XML EXPLICIT

O resultado é um fragmento XML no seguinte formato:

<Order Id="43659" Date="01/07/2001" Customer="676">
  <Item Id="43659" Product="776" Qty="1" Total="2024.994000" />
  <Item Id="43659" Product="777" Qty="3" Total="6074.982000" />
  <Item Id="43659" Product="778" Qty="1" Total="2024.994000" />
</Order>
<Order Id="43660" Date="01/07/2001" Customer="117">
  <Item Id="43660" Product="762" Qty="1" Total="419.458900" />
  <Item Id="43660" Product="758" Qty="1" Total="874.794000" />
</Order>
<Order Id="43661" Date="01/07/2001" Customer="442">
  <Item Id="43661" Product="745" Qty="1" Total="809.760000" />
  <Item Id="43661" Product="743" Qty="1" Total="714.704300" />
</Order>

Vimos neste artigo como podemos construir documentos XML customizados com uso da cláusula FOR XML EXPLICIT.