Transformando XML Fragments em dados relacionais

Podemos transformar fragmentos de XML em dados relacionais utilizando o comando nodes, associado ao tipo XML. Com o comando nodes, podemos consultar dados de um fragmento XML acessando os valores de cada tag pelo seu nome. Veja o exemplo abaixo:

DECLARE @xml XML

SELECT @xml='<Root>

  <cidades dt_venda="2007" grupo="Florianópolis" valor="100.32" />

  <cidades dt_venda="2007" grupo="São Paulo" valor="300.72" />

  <cidades dt_venda="2007" grupo="Rio de Janeiro" valor="700.82" />

  <cidades dt_venda="2008" grupo="Florianópolis" valor="800.32" />

  <cidades dt_venda="2008" grupo="São Paulo" valor="700.72" />

  <cidades dt_venda="2008" grupo="Rio de Janeiro" valor="660.82" />

  <cidades dt_venda="2009" grupo="Florianópolis" valor="340.32" />

  <cidades dt_venda="2009" grupo="São Paulo" valor="120.72" />

  <cidades dt_venda="2009" grupo="Rio de Janeiro" valor="30.82" />

</Root>'

 

 No fragmento acima, observamos um nó raiz denominado Root, e nós subseqüentes denominados cidades com atributos dt_venda, valor e grupo. Observe como podemos acessar estes dados através do comando nodes:

SELECT

      tab.col.value('@grupo','NVarchar(50)') as Grupo,

      tab.col.value('@dt_venda','NVarchar(4)') as Ano,

      tab.col.value('@valor','numeric(18,2)') as Valor

from

      @xml.nodes('/Root/cidades') as tab(col)

 

O resultado é um dataset com os dados do XML apresentados sob a forma de uma tabela. Porém, se decidirmos agrupar as cidades para obter o total de vendas de cada uma delas obtemos um erro, pois não podemos usar comandos de agrupamento com o comando nodes:

Msg 4148, Level 16, State 1, Line 14

XML methods are not allowed in a GROUP BY clause.

Podemos contornar este problema através de uma derived table:

SELECT

      cidade,

      SUM(valor) as valor

FROM

      (SELECT

        tab.col.value('@grupo','varchar(100)') as cidade

        ,tab.col.value('@valor','money') as valor

      FROM

        @xml.nodes('/Root/cidades') as tab(col)) as dados

GROUP BY

      cidade

 

Até a próxima!