Gerando XML no SQL Server – Arte do FOR XML EXPLICIT

Continuando a série Arte do FOR XML, partiremos em busca do EXPLICIT, que permite a criação de XML que obedecem a estruturas hierárquicas (pai-filho) de uma forma pouco descomplicada.

A arte do FOR XML EXPLICT começa com a definição de duas colunas de ordem estrutural, a Tag, para identificar o nó que estamos trabalhando, e Parent, que define qual seria o nó pai. Após definias as colunas estruturais, vamos para a geração do nó, no qual identificamos primeiramente o nome do nó, seguido do numero de identificação do nó (Tag) e o atributo a ser criado, conforme a sintaxe:

[NomeDoNó!Tag!NomeDoAtributo]

Exemplo:

SELECT TOP (2)
	  Tag = 1
	, Parent = NULL
	, PC.Name AS [Categoria!1!Nome] -- Definição do nó
FROM
	Production.ProductCategory AS PC
FOR XML EXPLICIT
	, ROOT('Estoque') -- Nó raiz
<Estoque>
  <Categoria Nome="Accessories" />
  <Categoria Nome="Bikes" />
</Estoque> -- Nó raiz

Para um cenário com mais de um nó, se faz necessária à utilização de UNION ALL entre o comando que constrói o primeiro nó e o comando que constrói o segundo nó:

SELECT TOP (2)
	  Tag = 1 -- Identificação do primeiro nó
	, Parent = NULL
	, PC.Name AS [Categoria1!1!Nome]
	, NULL AS [Categoria2!2!Nome] -- Declaração do segundo nó
FROM
	Production.ProductCategory AS PC
UNION ALL
SELECT TOP (2)
	  Tag = 2 -- Identificação do segundo nó
	, Parent = NULL
	, NULL AS [Categoria1!1!Nome] -- Repeticação do primeiro nó
	, PC.Name AS [Categoria2!2!Nome]
FROM
	Production.ProductCategory AS PC
FOR XML EXPLICIT
	, ROOT('Estoque')
<Estoque>
  <Categoria1 Nome="Accessories" />
  <Categoria1 Nome="Bikes" />
  <Categoria2 Nome="Accessories" />
  <Categoria2 Nome="Bikes" />
</Estoque>

Para questões de hierarquia, o nó filho deve ter definida sua relação com o nó pai, através da coluna estrutural Parent:

SELECT
	  Tag = 1 -- Identificação do primeiro nó
	, Parent = NULL
	, Pai.Name AS [Pai!1!Name]
	, NULL AS [Filho!2!Name]
FROM
	Production.ProductCategory AS Pai
UNION ALL
SELECT TOP (20)
	  Tag = 2
	, Parent = 1 -- Definição de hierarquia
	, Pai.Name AS [Pai!1!Name]
	, Filho.Name AS [Filho!2!Name]
FROM
	Production.ProductCategory AS Pai
	INNER JOIN Production.ProductSubcategory AS Filho
	ON Pai.ProductCategoryID = Filho.ProductCategoryID
FOR XML EXPLICIT
	, ROOT('Estoque')
<Estoque>
  <Pai Name="Accessories" />
  <Pai Name="Bikes" />
  <Pai Name="Clothing" />
  <Pai Name="Components">
    <Filho Name="Bike Racks" />
    <Filho Name="Bike Stands" />
    <Filho Name="Bottles and Cages" />
    <Filho Name="Cleaners" />
    <Filho Name="Fenders" />
    <Filho Name="Helmets" />
    <Filho Name="Hydration Packs" />
    <Filho Name="Lights" />
    <Filho Name="Locks" />
    <Filho Name="Panniers" />
    <Filho Name="Pumps" />
    <Filho Name="Tires and Tubes" />
    <Filho Name="Mountain Bikes" />
    <Filho Name="Road Bikes" />
    <Filho Name="Touring Bikes" />
    <Filho Name="Bib-Shorts" />
    <Filho Name="Caps" />
    <Filho Name="Gloves" />
    <Filho Name="Jerseys" />
    <Filho Name="Shorts" />
  </Pai>
</Estoque>

Conforme verificamos, foi criada a relação de pai para filho, mas não foi obedecida a real relação entre os registros (o registro Components ficou erroneamente como pai de todos os registros filhos), assim é recomendado utilizar um ORDER BY sobre um atributo do pai (seguido não obrigatoriamente de uma ordenação sobre o filho), da seguinte forma:

SELECT
	  Tag = 1
	, Parent = NULL
	, Pai.Name AS [Pai!1!Name]
	, NULL AS [Filho!2!Name]
FROM
	Production.ProductCategory AS Pai
UNION ALL
SELECT TOP (20)
	  Tag = 2
	, Parent = 1
	, Pai.Name AS [Pai!1!Name]
	, Filho.Name AS [Filho!2!Name]
FROM
	Production.ProductCategory AS Pai
	INNER JOIN Production.ProductSubcategory AS Filho
	ON Pai.ProductCategoryID = Filho.ProductCategoryID
ORDER BY [Pai!1!Name], [Filho!2!Name] -- Ordenação da hierarquia
FOR XML EXPLICIT
	, ROOT('Estoque')
<Estoque>
  <Pai Name="Accessories">
    <Filho Name="Bike Racks" />
    <Filho Name="Bike Stands" />
    <Filho Name="Bottles and Cages" />
    <Filho Name="Cleaners" />
    <Filho Name="Fenders" />
    <Filho Name="Helmets" />
    <Filho Name="Hydration Packs" />
    <Filho Name="Lights" />
    <Filho Name="Locks" />
    <Filho Name="Panniers" />
    <Filho Name="Pumps" />
    <Filho Name="Tires and Tubes" />
  </Pai>
  <Pai Name="Bikes">
    <Filho Name="Mountain Bikes" />
    <Filho Name="Road Bikes" />
    <Filho Name="Touring Bikes" />
  </Pai>
  <Pai Name="Clothing">
    <Filho Name="Bib-Shorts" />
    <Filho Name="Caps" />
    <Filho Name="Gloves" />
    <Filho Name="Jerseys" />
    <Filho Name="Shorts" />
  </Pai>
  <Pai Name="Components" />
</Estoque>

Por fim, temos a definição de uma hierarquia com um pai de dois filhos distintos:

SELECT TOP 1
	  Tag = 1
	, Parent = NULL -- Pai
	, PC.Name AS [PC!1!Name]
	, NULL AS [PSC!2!Name]
	, NULL AS [PSD!3!Name]
FROM
	Production.ProductCategory AS PC
UNION ALL
SELECT TOP 1
	  Tag = 2
	, Parent = 1 -- Filho 1
	, PC.Name AS [PC!1!Name]
	, PSC.Name AS [PSC!2!Name]
	, NULL AS [PSD!3!Name]
FROM
	Production.ProductCategory AS PC
	INNER JOIN Production.ProductSubcategory AS PSC
	ON PC.ProductCategoryID = PSC.ProductCategoryID
UNION ALL
SELECT TOP 1
	  Tag = 3
	, Parent = 1 -- Filho 2
	, PC.Name AS [PC!1!Name]
	, PSC.Name AS [PSC!2!Name]
	, PSC.Name AS [PSD!3!Name]
FROM
	Production.ProductCategory AS PC
	INNER JOIN Production.ProductSubcategory AS PSC
	ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY [PC!1!Name], [PSC!2!Name], [PSD!3!Name]
FOR XML EXPLICIT
	, ROOT('Estoque')
<Estoque>
  <PC Name="Accessories"> -- Pai
    <PSC Name="Bike Racks" /> -- Filho 1
    <PSD Name="Bike Racks" /> -- Filho 2
  </PC>
</Estoque>

E a hierarquia de três níveis, com pai, filho e neto:

SELECT TOP 1
	  Tag = 1
	, Parent = NULL -- Pai
	, PC.Name AS [PC!1!Name]
	, NULL AS [PSC!2!Name]
	, NULL AS [PSD!3!Name]
FROM
	Production.ProductCategory AS PC
UNION ALL
SELECT TOP 1
	  Tag = 2
	, Parent = 1 -- Filho
	, PC.Name AS [PC!1!Name]
	, PSC.Name AS [PSC!2!Name]
	, NULL AS [PSD!3!Name]
FROM
	Production.ProductCategory AS PC
	INNER JOIN Production.ProductSubcategory AS PSC
	ON PC.ProductCategoryID = PSC.ProductCategoryID
UNION ALL
SELECT TOP 1
	  Tag = 3
	, Parent = 2 -- Neto
	, PC.Name AS [PC!1!Name]
	, PSC.Name AS [PSC!2!Name]
	, PSC.Name AS [PSD!3!Name]
FROM
	Production.ProductCategory AS PC
	INNER JOIN Production.ProductSubcategory AS PSC
	ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY [PC!1!Name], [PSC!2!Name], [PSD!3!Name]
FOR XML EXPLICIT
	, ROOT('Estoque')
<Estoque>
  <PC Name="Accessories"> -- Pai
    <PSC Name="Bike Racks"> -- Filho
      <PSD Name="Bike Racks" /> -- Neto
    </PSC>
  </PC>
</Estoque>

3 pensamentos sobre “Gerando XML no SQL Server – Arte do FOR XML EXPLICIT

  1. Pingback: Arte do FOR XML – Resumo « SQL From Hell.com

  2. Pingback: Extraindo informações de arquivo XML para o SQL Server « Alex Souza

  3. Pingback: Como atribuir a uma variável no SQL Server, o valor de uma consulta FOR XML | SQL From Hell.com

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s