Gerando XML no SQL Server – Arte do FOR XML AUTO

Como é inevitável se falar de Service Broker sem falar em XML, vou abordar nos próximos artigos a arte de gerar XML por T-SQL, que é útil para formatar dados diretamente pelo banco de dados, principalmente no caso de criar mensagens para o Service Broker ou XML para as aplicações.

Abordarei inicialmente as quatro formas básicas de geração de XML por meio da clausula FOR XML, e de acordo com a evolução dos artigos, partiremos para as técnicas de leitura e otimização.

Neste primeiro passo conheceremos o FOR XML AUTO, que é a forma mais simples para gerar XML por meio de consultas, exemplo:

SELECT TOP (5)
	  FirstName
	, LastName
FROM
	Person.Contact
FOR XML AUTO

<Person.Contact FirstName="Gustavo" LastName="Achong" />
<Person.Contact FirstName="Catherine" LastName="Abel" />
<Person.Contact FirstName="Kim" LastName="Abercrombie" />
<Person.Contact FirstName="Humberto" LastName="Acevedo" />
<Person.Contact FirstName="Pilar" LastName="Ackerman" />

Com a consulta seguinte, criaremos um nó raiz para nosso XML:

SELECT TOP (5)
	  FirstName
	, LastName
FROM
	Person.Contact
FOR XML AUTO
	, ROOT('Person') -- nó raiz

<Person> -- nó raiz
  <Person.Contact FirstName="Gustavo" LastName="Achong" />
  <Person.Contact FirstName="Catherine" LastName="Abel" />
  <Person.Contact FirstName="Kim" LastName="Abercrombie" />
  <Person.Contact FirstName="Humberto" LastName="Acevedo" />
  <Person.Contact FirstName="Pilar" LastName="Ackerman" />
</Person>

Separando as colunas em nós:

SELECT TOP (3)
	  FirstName
	, LastName
	, EmailAddress
FROM
	Person.Contact
FOR XML AUTO
	, ELEMENTS -- Separando em nós
	, ROOT('Person')

<Person>
  <Person.Contact>
    <FirstName>Gustavo</FirstName>
    <LastName>Achong</LastName>
    <EmailAddress>gustavo0@adventure-works.com</EmailAddress>
  </Person.Contact>
  <Person.Contact>
    <FirstName>Catherine</FirstName>
    <LastName>Abel</LastName>
    <EmailAddress>catherine0@adventure-works.com</EmailAddress>
  </Person.Contact>
  <Person.Contact>
    <FirstName>Kim</FirstName>
    <LastName>Abercrombie</LastName>
    <EmailAddress>kim2@adventure-works.com</EmailAddress>
  </Person.Contact>
</Person>

E como utilizar alias/apelidos para definir os nomes dos nós:

SELECT TOP (5)
	  [Name] = FirstName + ', ' + LastName -- Alias na coluna
	, [Email] = EmailAddress
FROM
	Person.Contact AS [Contact] -- Alias na tabela
FOR XML AUTO
	, ELEMENTS
	, ROOT('Person')

<Person>
  <Contact> -- Alias da tabela
    <Name>Gustavo, Achong</Name> -- Alias da coluna
    <Email>gustavo0@adventure-works.com</Email>
  </Contact>
  <Contact>
    <Name>Catherine, Abel</Name>
    <Email>catherine0@adventure-works.com</Email>
  </Contact>
  <Contact>
    <Name>Kim, Abercrombie</Name>
    <Email>kim2@adventure-works.com</Email>
  </Contact>
  <Contact>
    <Name>Humberto, Acevedo</Name>
    <Email>humberto0@adventure-works.com</Email>
  </Contact>
  <Contact>
    <Name>Pilar, Ackerman</Name>
    <Email>pilar1@adventure-works.com</Email>
  </Contact>
</Person>

Para ficar mais complexo o cenário, a utilização de JOINs, que segundo a ordem dos elementos que forem informados no SELECT, alteraremos a ordem da “hierarquia” dos nós:

Exemplo 1: PC como pai, PSC como filho:

SELECT TOP (5)
	  PC.Name, -- Pai
	  PSC.Name -- Filho
FROM
	Production.ProductCategory AS PC
	INNER JOIN Production.ProductSubcategory AS PSC
	ON PC.ProductCategoryID = PSC.ProductCategoryID
FOR XML AUTO
	, ROOT('P1')

<P1>
  <PC Name="Accessories"> -- Pai
    <PSC Name="Bike Racks" /> -- Filho
    <PSC Name="Bike Stands" />
    <PSC Name="Bottles and Cages" />
    <PSC Name="Cleaners" />
    <PSC Name="Fenders" />
  </PC>
</P1>

Exemplo 2: PSC como pai, PC como filho:

SELECT TOP (5)
	  PSC.Name, -- Pai
	  PC.Name -- Filho
FROM
	Production.ProductCategory AS PC
	INNER JOIN Production.ProductSubcategory AS PSC
	ON PC.ProductCategoryID = PSC.ProductCategoryID
FOR XML AUTO
	, ROOT('P1')

<P1>
  <PSC Name="Bike Racks"> -- Pai
    <PC Name="Accessories" /> -- Filho
  </PSC>
  <PSC Name="Bike Stands">
    <PC Name="Accessories" />
  </PSC>
  <PSC Name="Bottles and Cages">
    <PC Name="Accessories" />
  </PSC>
  <PSC Name="Cleaners">
    <PC Name="Accessories" />
  </PSC>
  <PSC Name="Fenders">
    <PC Name="Accessories" />
  </PSC>
</P1>

Obs.: Veja que a ordem do JOIN não afeta em nada, pois somente foi alterada a ordem do SELECT do primeiro exemplo para o segundo.

Exemplo 3: Vários níveis:

SELECT TOP (2)
	  PSC.Name, -- Pai
	  PC.Name, -- Filho
	  PSC2.Name -- Neto
FROM
	Production.ProductCategory AS PC
	INNER JOIN Production.ProductSubcategory AS PSC
	ON PC.ProductCategoryID = PSC.ProductCategoryID
	INNER JOIN Production.ProductSubcategory AS PSC2
	ON PSC2.ProductSubcategoryID = PSC.ProductSubcategoryID
FOR XML AUTO
	, ROOT('P1')

<P1>
  <PSC Name="Bike Racks"> -- Pai
    <PC Name="Accessories"> -- Filho
      <PSC2 Name="Bike Racks" /> -- Neto
    </PC>
  </PSC>
  <PSC Name="Bike Stands">
    <PC Name="Accessories">
      <PSC2 Name="Bike Stands" />
    </PC>
  </PSC>
</P1>