Representando dados em XML no SQL Server

Neste artigo veremos como utilizar a instrução FOR XML no SQL Server para representar o resultado de consultas no formato XML.

por Joel Rodrigues

Na maioria (se não em todos) os gerenciadores de bancos de dados relacionais, quando efetuamos uma consulta (query) os resultados são apresentados no formato de grids (ou tabelas), com os dados organizados em linhas e colunas. Alguns, como o SQL Server, ainda permitem que se obtenha esse resultado com uma formatação diferente, como em texto tabulado, como vemos na Figura 1, ou exportando direto para arquivo.

Figura 1. Formatos de exibição de queries do SQL Server

Geralmente, a disposição dos dados em forma de tabela já é suficiente para a obtenção dos resultados esperados, que muitas vezes se resumem à análise superficial das informações filtradas. Porém, existem situações onde é necessário visualizar e exportar os resultados em um formato mais bem estruturado, que possa ser lido por indivíduos com pouco conhecimento técnico, bem como que possa ser utilizado facilmente por outras aplicações, independente de plataforma.

O XML atende bem a esses requisitos e é atualmente um dos formatos mais utilizados para tráfego de dados entre aplicações (inclusive aplicações escritas em diferentes linguagens e ambientes). Para atender a esse tipo de situação, o SQL Server oferece amplo suporte à formatação de dados em XML, permitindo que o resultado de consultas seja facilmente organizado, lido e preparado para posterior exportação.

XML: uma rápida revisão

XML (eXtensible Markup Language) é uma linguagem de marcação cujo principal propósito é o compartilhamento de informações em um formato que permita representar os dados de forma hierárquica e que possa ser lido de forma simples.

Atualmente a maior parte das linguagens de programação permitem trabalhar com XML para representação de dados e, principalmente, para o intercâmbio de informações.

Essa linguagem baseia-se em tags e as informações podem ser representadas na forma de elementos e atributos, como mostra a Listagem 1.

Listagem 1. Representação de dados em XML

  <clientes>
         <cliente codigo="1" nome="João"/>
         <cliente codigo="2" nome="Pedro"/>
         <cliente codigo="3" nome="Carlos"/>
  </clientes>

Nesse exemplo cada tag representa um elemento, enquanto o código e o nome são atributos.

Resultados em XML no SQL Server

Para formatar os resultados de uma consulta como XML é preciso utilizar, após o SELECT, a instrução FOR XML, seguida do formato específico desejado. Este formato pode assumir um dos seguintes valores:

• RAW;
• AUTO;
• EXPLICIT;
• PATH.

Os formatos citados indicam o modo como o resultado será organizado, uma vez que o padrão XML é bastante flexível e permite que uma mesma amostra de dados seja exibida com diferentes configurações.

RAW

O modo de representação RAW faz com que cada linha da consulta seja transformada em um elemento nomeado de forma genérica como row, enquanto que cada coluna passa a representar um atributo desse elemento. A Listagem 2 mostra um exemplo prático de uso do RAW numa consulta de itens vendidos.

Listagem 2. Utilizando o modo RAW básico

  SELECT
         Vendas.ID AS Venda, 
         ItensVenda.ID_Produto AS Produto,
         ItensVenda.Valor,
         ItensVenda.Quantidade
  FROM 
         ItensVenda
         INNER JOIN Vendas ON ItensVenda.ID_Venda = Vendas.ID
  FOR XML RAW

O resultado dessa consulta, que pode ser visto na Listagem 3, são todos os itens vendidos representados por elementos XML nomeados como row, e as colunas Venda, Produto, Valor e Quantidade representadas como atributos.

Listagem 3. Resultado do uso do RAW básico

  <row Venda="1" Produto="0101" Valor="10" Quantidade="2" />
  <row Venda="1" Produto="1111" Valor="20" Quantidade="1" />
  <row Venda="1" Produto="2233" Valor="60" Quantidade="1" />
  <row Venda="2" Produto="2233" Valor="60" Quantidade="2" />
  <row Venda="2" Produto="1111" Valor="20" Quantidade="1" />
  <row Venda="2" Produto="0101" Valor="10" Quantidade="6" />
  <row Venda="3" Produto="4444" Valor="50" Quantidade="1" />
  <row Venda="4" Produto="9988" Valor="15" Quantidade="2" />

Para fins práticos, porém, geralmente será necessário renomear os elementos de acordo com o que realmente representam, ao invés de utilizar a nomenclatura genérica row. Para isso, basta indicar após o RAW, o nome que os elementos devem ter. Além disso, também é possível que as colunas sejam representadas como elementos (filhos do elemento que representa a linha), ao invés de atributos, bastando utilizar a diretiva ELEMENTS. Na Listagem 4 temos o exemplo anterior alterado para utilizar essas opções, e na Listagem 5 vemos o resultado.

Listagem 4. Uso do RAW com diretivas adicionais

  SELECT
         Vendas.ID AS Venda, 
         ItensVenda.ID_Produto AS Produto,
         ItensVenda.Valor,
         ItensVenda.Quantidade
  FROM 
         ItensVenda
         INNER JOIN Vendas ON ItensVenda.ID_Venda = Vendas.ID
  FOR XML RAW ('ItemVenda'), ELEMENTS

Listagem 5. Resultado da diretiva ELEMENTS e elementos renomeados

  <ItemVenda>
    <Venda>1</Venda>
    <Produto>0101</Produto>
    <Valor>10</Valor>
    <Quantidade>2</Quantidade>
  </ItemVenda>
  <ItemVenda>
    <Venda>1</Venda>
    <Produto>1111</Produto>
    <Valor>20</Valor>
    <Quantidade>1</Quantidade>
  </ItemVenda>
  <ItemVenda>
    <Venda>1</Venda>
    <Produto>2233</Produto>
    <Valor>60</Valor>
    <Quantidade>1</Quantidade>
  </ItemVenda>

AUTO

O AUTO retorna o resultado da consulta na forma de elementos aninhados hierarquicamente, onde cada tabela envolvida é representada como um elemento e suas colunas como atributos (ou elementos filhos se usarmos a diretiva ELEMENTS no final, assim como no RAW).

Este formato não permite grandes customizações sobre o formato do XML resultante, portanto é útil quando se deseja obter uma representação simples e de forma rápida, mantendo as características originais da tabelas e colunas. Na Listagem 6 vemos um exemplo de uso do AUTO e seu resultado na Listagem 7.

Listagem 6. Uso do modo AUTO

  SELECT
         Vendas.ID AS Venda, 
         ItensVenda.ID_Produto AS Produto,
         ItensVenda.Valor,
         ItensVenda.Quantidade
  FROM 
         ItensVenda
         INNER JOIN Vendas ON ItensVenda.ID_Venda = Vendas.ID
  FOR XML AUTO

Listagem 7. Resultado do modo AUTO

  <Vendas Venda="1">
    <ItensVenda Produto="0101" Valor="10" Quantidade="2" />
    <ItensVenda Produto="1111" Valor="20" Quantidade="1" />
    <ItensVenda Produto="2233" Valor="60" Quantidade="1" />
  </Vendas>
  <Vendas Venda="2">
    <ItensVenda Produto="2233" Valor="60" Quantidade="2" />
    <ItensVenda Produto="1111" Valor="20" Quantidade="1" />
    <ItensVenda Produto="0101" Valor="10" Quantidade="6" />
  </Vendas>
  <Vendas Venda="3">
    <ItensVenda Produto="4444" Valor="50" Quantidade="1" />
  </Vendas>
  <Vendas Venda="4">
    <ItensVenda Produto="9988" Valor="15" Quantidade="2" />
  </Vendas>

Note que esse modo nos traz um resultado mais adequado para a representação dos dados desse exemplo, uma vez que apresenta os itens como elementos filhos de cada venda.

EXPLICIT

O modo EXPLICIT oferece uma forma mais flexível, porém mais complexa, de formar o XML resultante da consulta. Enquanto os modos AUTO e RAW garante a estrutura do XML bem formada e com uma estrutura padrão, o EXPLICIT requer que o formato do XML seja especificado na consulta, de forma a obter o resultado esperado.

Para montar a estrutura desejada, é necessário criar campos adicionais, nomear os elementos/atributos e definir seu nível na hierarquia. Na Listagem 8 temos um exemplo de uso.

Listagem 8. Exemplo de uso do EXPLICIT

  SELECT
      1 AS Tag,
      NULL AS Parent,
      NULL AS [ItensVendidos!1!],
      NULL AS [Item!2!Venda],
      NULL AS [Item!2!Produto],
      NULL AS [Item!2!Valor],
      NULL AS [Item!2!Quantidade]
  UNION ALL
  SELECT
         2 AS Tag,
         1 AS Parent,
         NULL,
         Vendas.ID AS Codigo,
         ItensVenda.ID_Produto,
         ItensVenda.Valor,
         ItensVenda.Quantidade
  FROM 
         ItensVenda
         INNER JOIN Vendas ON ItensVenda.ID_Venda = Vendas.ID
  FOR XML EXPLICIT

Nesse exemplo criamos uma tag ItensVenda no primeiro nível da hierarquia, e representamos os itens como elementos filhos no segundo nível. O resultado é visto na Listagem 9.

Listagem 9. Resultado do EXPLICIT

  <ItensVendidos>
    <Item Venda="1" Produto="0101" Valor="10" Quantidade="2" />
    <Item Venda="1" Produto="1111" Valor="20" Quantidade="1" />
    <Item Venda="1" Produto="2233" Valor="60" Quantidade="1" />
    <Item Venda="2" Produto="2233" Valor="60" Quantidade="2" />
    <Item Venda="2" Produto="1111" Valor="20" Quantidade="1" />
    <Item Venda="2" Produto="0101" Valor="10" Quantidade="6" />
    <Item Venda="3" Produto="4444" Valor="50" Quantidade="1" />
    <Item Venda="4" Produto="9988" Valor="15" Quantidade="2" />
  </ItensVendidos> 

PATH

O PATH, assim como o EXPLICIT, permite maiores customizações na estrutura do XML resultante, porém com uma sintaxe mais simples, se comparado ao EXPLICIT. Com este modo pode-se facilmente representar dados de forma hierárquica, incluindo subqueries, e renomear os elementos e atributos utilizando aliases.

Na Listagem 10 temos um exemplo de consulta usando o PATH. Note que é possível aninhar consultas e redefinir o elemento raiz do resultado. E se comparado ao modo EXPLICIT, a sintaxe é bem mais intuitiva.

Listagem 10. Exemplo de uso do PATH

  SELECT
         Vendas.ID AS '@Codigo',
         (
               SELECT
                      ID_Produto AS '@Referencia',
                      Quantidade AS '@Quantidade',
                      Valor AS '@Valor'
               FROM ItensVenda
               WHERE ID_Venda = Vendas.ID
               FOR XML PATH ('Produto'), TYPE
         ) AS 'Itens'
  FROM Vendas
  FOR XML PATH ('Venda'), root('Vendas')

Na subquery que retorna os itens da venda também aplicamos o FOR XML PATH, fazendo assim com que os resultados também sejam representados como XML. A diretiva TYPE faz com que essa subquery seja tratada como uma propriedade do item mais externo e com isso sejam exibidos como XML, sem ela os resultados seriam exibidos como texto puro.

Após o PATH, podemos nomear os elementos (Produto e Venda), bem como definir o nome do elemento raiz, que nesse caso é a tag Vendas. O resultado pode ser visto na Listagem 11.

Listagem 11. Resultado do uso do PATH

  <Vendas>
    <Venda Codigo="1">
      <Itens>
        <Produto Referencia="0101" Quantidade="2" Valor="10" />
        <Produto Referencia="1111" Quantidade="1" Valor="20" />
        <Produto Referencia="2233" Quantidade="1" Valor="60" />
      </Itens>
    </Venda>
    <Venda Codigo="2">
      <Itens>
        <Produto Referencia="2233" Quantidade="2" Valor="60" />
        <Produto Referencia="1111" Quantidade="1" Valor="20" />
        <Produto Referencia="0101" Quantidade="6" Valor="10" />
      </Itens>
    </Venda>
    <Venda Codigo="3">
      <Itens>
        <Produto Referencia="4444" Quantidade="1" Valor="50" />
      </Itens>
    </Venda>
    <Venda Codigo="4">
      <Itens>
        <Produto Referencia="9988" Quantidade="2" Valor="15" />
      </Itens>
    </Venda>
  </Vendas>

O uso da instrução FOR XML em consultas pode facilitar, por exemplo, a exportação de dados para outras aplicações. Esse recurso pode ser usado tanto por desenvolvedores que desejam implementar certas funcionalidades em suas aplicações, quanto por DBAs ou qualquer pessoa responsável por efetuar consultas e extrair informações de uma base de dados.

Em geral, quando se exporta dados para outras aplicações, deve-se seguir um esquema de formação do XML. Conhecendo a estrutura que deve ter o XML gerado, basta identificar qual dos modos é o mais adequado e fazer as devidas customizações.

Links

Extensible Markup Language (XML) – W3C
http://www.w3.org/TR/REC-xml/

FOR XML (SQL Server) – MSDN
https://msdn.microsoft.com/en-us/library/ms178107.aspx