Olá pessoal,
Boa noite!
Neste post vou demonstrar pra vocês como importar, ler, tratar e exportar dados entre uma tabela do SQL Server e um arquivo XML.
Essa integração entre o banco de dados e arquivos XML é um excelente recurso e grande diferencial para desenvolvedores que utilizam o SQL Server e podem facilmente ler e gerar arquivos nesse formato nativamente pelo banco de dados.
Conhecendo os arquivos XML de exemplo Como importar arquivos XML para o SQL Server Como tratar e ler os atributos de um XML no SQL ServerComo tratar e ler os dados de atributos de um XML no SQL Server
Uma vez que o XML já está em uma tabela, vamos iniciar o tratamento e a leitura das informações do XML. Nesse caso, precisaremos armazenar o conteúdo em uma variável do tipo XML.
Para isso, você pode fazer assim:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
Agora que conseguimos carregar nosso XML para uma variável, vamos iniciar o tratamento dos dados.
Leitura simples:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
SELECT
@XML.value('(/Root/Cliente[1]/@Nome)[1]','varchar(100)') AS Cliente1_Nome,
@XML.value('(/Root/Cliente[1]/@Idade)[1]','int') AS Cliente1_Idade,
@XML.value('(/Root/Cliente[1]/@CPF)[1]','varchar(100)') AS Cliente1_CPF,
@XML.value('(/Root/Cliente[1]/@Email)[1]','varchar(100)') AS Cliente1_Email,
@XML.value('(/Root/Cliente[1]/@Celular)[1]','varchar(100)') AS Cliente1_Celular,
@XML.value('(/Root/Cliente[1]/Endereco[1]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco1_Cidade,
@XML.value('(/Root/Cliente[1]/Endereco[2]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco2_Cidade
SELECT
@XML.value('(/Root/Cliente[2]/@Nome)[1]','varchar(100)') AS Cliente2_Nome,
@XML.value('(/Root/Cliente[2]/@Idade)[1]','int') AS Cliente2_Idade,
@XML.value('(/Root/Cliente[2]/@CPF)[1]','varchar(100)') AS Cliente2_CPF,
@XML.value('(/Root/Cliente[2]/@Email)[1]','varchar(100)') AS Cliente2_Email,
@XML.value('(/Root/Cliente[2]/@Celular)[1]','varchar(100)') AS Cliente2_Celular,
@XML.value('(/Root/Cliente[2]/Endereco[1]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco1_Cidade,
@XML.value('(/Root/Cliente[2]/Endereco[2]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco2_Cidade
Como vimos no exemplo acima, precisamos definir manualmente a linha que queremos retornar informações do nosso XML. Mas e se o arquivo tiver, 100, 1.000 ou mais linhas? Teremos que utilizar a instrução WHILE para percorrer todas as linhas do XML? Não. Para isso, podemos utilizar a função NODES, que aplica os filtros do nosso SELECT em todos os nós do nosso seletor e os retorna em forma de linhas de uma tabela:
Utilizando a função NODES para retornar todos os nós:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
SELECT
Clientes.linha.value('@Nome','varchar(100)') AS Nome,
Clientes.linha.value('@Idade','int') AS Idade,
Clientes.linha.value('@CPF','varchar(14)') AS CPF,
Clientes.linha.value('@Email','varchar(200)') AS Email,
Clientes.linha.value('@Celular','varchar(20)') AS Celular
FROM
@XML.nodes('/Root/Cliente') Clientes(linha)
Ao utilizarmos a função nodes, estamos informando que para cada nó da árvore do XML que seja na estrutura “/Root/Cliente” seja retornado um registro da nossa tabela. Esse registro é identificado utilizando a nomenclatura Clientes (elemento pai, que seria como se fosse a tabela) e linha (cada registro do elemento pai).
Após quebrar os nós em registros, precisamos recuperar as informações de cada atributo. Para isso, utilizamos a função value, seguido por @NomeDoAtributo (deve estar igual ao nome do atributo do XML) e o seu respectivo tipo de retorno.
Utilizando a função EXIST
Em determinadas situações, precisamos saber se um determinado nó existe no nosso XML. Para isso, o SQL Server nos disponibiliza a função exist, que nos permite realizar esse tipo de verificação:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
SELECT
@XML.exist('(/Root/Cliente[1]/@Nome)[1]') AS Cliente1_Existe,
@XML.exist('(/Root/Cliente[2]/@Nome)[1]') AS Cliente2_Existe,
@XML.exist('(/Root/Cliente[3]/@Nome)[1]') AS Cliente3_Existe
Utilizando exist junto com o value:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
SELECT
(CASE WHEN @XML.exist('(/Root/Cliente[1]/@Nome)[1]') = 1
THEN @XML.value('(/Root/Cliente[1]/@Nome)[1]', 'varchar(100)')
ELSE 'Não existe'
END) AS Cliente1_Nome,
(CASE WHEN @XML.exist('(/Root/Cliente[2]/@Nome)[1]') = 1
THEN @XML.value('(/Root/Cliente[2]/@Nome)[1]', 'varchar(100)')
ELSE 'Não existe'
END) AS Cliente2_Nome,
(CASE WHEN @XML.exist('(/Root/Cliente[3]/@Nome)[1]') = 1
THEN @XML.value('(/Root/Cliente[3]/@Nome)[1]', 'varchar(100)')
ELSE 'Não existe'
END) AS Cliente3_Nome
Recuperando os nós filhos
A primeira parte foi concluída: Já conseguimos realizar uma importação simples no nosso XML. Mas ainda temos muito a avançar: Nosso XML possui N nós filhos, com mais 2 níveis de hierarquia para trabalharmos (Endereco e Telefone).
Importando o 1º nível de hierarquia: Endereco
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
SELECT
Clientes.linha.value('@Nome','varchar(100)') AS Nome,
Clientes.linha.value('@Idade','int') AS Idade,
Clientes.linha.value('@CPF','varchar(14)') AS CPF,
Clientes.linha.value('@Email','varchar(200)') AS Email,
Clientes.linha.value('@Celular','varchar(20)') AS Celular,
Enderecos.linha.value('@Cidade','varchar(60)') AS Cidade,
Enderecos.linha.value('@Estado','varchar(2)') AS UF,
Enderecos.linha.value('@Pais','varchar(50)') AS Pais,
Enderecos.linha.value('@CEP','varchar(10)') AS CEP
FROM
@XML.nodes('/Root/Cliente') Clientes(linha)
CROSS APPLY Clientes.linha.nodes('Endereco') Enderecos(linha)
Reparem que para pegar os subníveis da hierarquia eu utilizo um CROSS APPLY do elemento pai (Clientes) e para cada registro (linha) eu faço um cruzamento desses dados com os nós filhos (do tipo Endereco).
Importando o 2º nível de hierarquia: Telefone
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
SELECT
Clientes.linha.value('@Nome','varchar(100)') AS Nome,
Clientes.linha.value('@Idade','int') AS Idade,
Clientes.linha.value('@CPF','varchar(14)') AS CPF,
Clientes.linha.value('@Email','varchar(200)') AS Email,
Clientes.linha.value('@Celular','varchar(20)') AS Celular,
Enderecos.linha.value('@Cidade','varchar(60)') AS Cidade,
Enderecos.linha.value('@Estado','varchar(2)') AS UF,
Enderecos.linha.value('@Pais','varchar(50)') AS Pais,
Enderecos.linha.value('@CEP','varchar(10)') AS CEP,
Telefones.linha.value('@Fixo','varchar(20)') AS Telefone_Fixo,
Telefones.linha.value('@Piscina','varchar(20)') AS Tem_Piscina,
Telefones.linha.value('@Quintal','varchar(20)') AS Tem_Quintal,
Telefones.linha.value('@Quadra','varchar(20)') AS Tem_Quadra,
Telefones.linha.value('@NaoExiste','varchar(20)') AS Atributo_Nao_Existe
FROM
@XML.nodes('/Root/Cliente') Clientes(linha)
CROSS APPLY Clientes.linha.nodes('Endereco') Enderecos(linha)
CROSS APPLY Enderecos.linha.nodes('Telefone') Telefones(linha)
E aí conseguimos finalmente importar os dados. Mas tem um erro aí..
Se analisarmos melhor nosso XML, vamos ver que um dos endereços não foi importado, porque ele não tinha telefone e como fizemos um CROSS APPLY, esse registro foi ignorado. Vamos precisar tratar isso:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)
SELECT
Clientes.linha.value('@Nome','varchar(100)') AS Nome,
Clientes.linha.value('@Idade','int') AS Idade,
Clientes.linha.value('@CPF','varchar(14)') AS CPF,
Clientes.linha.value('@Email','varchar(200)') AS Email,
Clientes.linha.value('@Celular','varchar(20)') AS Celular,
Enderecos.linha.value('@Cidade','varchar(60)') AS Cidade,
Enderecos.linha.value('@Estado','varchar(2)') AS UF,
Enderecos.linha.value('@Pais','varchar(50)') AS Pais,
Enderecos.linha.value('@CEP','varchar(10)') AS CEP,
Telefones.linha.value('@Fixo','varchar(20)') AS Telefone_Fixo,
Telefones.linha.value('@Piscina','varchar(20)') AS Tem_Piscina,
Telefones.linha.value('@Quintal','varchar(20)') AS Tem_Quintal,
Telefones.linha.value('@Quadra','varchar(20)') AS Tem_Quadra,
Telefones.linha.value('@NaoExiste','varchar(20)') AS Atributo_Nao_Existe
FROM
@XML.nodes('/Root/Cliente') Clientes(linha)
CROSS APPLY Clientes.linha.nodes('Endereco') Enderecos(linha)
OUTER APPLY Enderecos.linha.nodes('Telefone') Telefones(linha)
Para resolver essa situação, substituí o último CROSS APPLY por um OUTER APPLY, que possui um comportamento semelhante a um LEFT JOIN nesse caso, onde os registros que não tiverem um nó filho continuam aparecendo na nossa tabela, e as colunas destinadas aos nós filhos ficarão vazias. Sempre que houver uma situação onde você não tem certeza que o elemento terá filhos ou não, utilize o OUTER APPLY.
Como tratar e ler os dados de um XML no SQL Server
Diferente da base 1, onde as informações eram todas guardadas em forma de atributos, vamos agora utilizar o XML da base 2, que é predominantemente formado por dados.
Leitura simples dos dados
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
@XML.value('(Escola/Turma/Aluno)[1]', 'varchar(60)') AS Aluno1,
@XML.value('(Escola/Turma/Aluno)[2]', 'varchar(60)') AS Aluno2,
@XML.value('(Escola/Turma/Aluno)[3]', 'varchar(60)') AS Aluno3,
@XML.value('(Escola/Turma/Aluno)[4]', 'varchar(60)') AS Aluno4,
@XML.value('(Escola/Turma/Aluno)[5]', 'varchar(60)') AS Aluno5,
@XML.value('(Escola/Turma/Aluno)[6]', 'varchar(60)') AS Aluno6,
@XML.value('(Escola/Turma/Aluno)[14]', 'varchar(60)') AS Aluno14_Nao_Existe
Assim como fiz com os atributos, quis demonstrar como retornar individualmente cada nó do arquivo, especificando a índice da sua posição em relação à raiz. Agora vou demonstrar novamente como utilizar a função NODES para retornar todos os nós como registros de uma tabela:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
Alunos.linha.value('.','varchar(100)') AS Nome
FROM
@XML.nodes('/Escola/Turma/Aluno') Alunos(linha)
Exemplo mais completo, misturando dados e atributos:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
Alunos.linha.value('.', 'varchar(60)') AS Aluno,
Turmas.linha.value('@Nome','varchar(100)') AS Turma,
Turmas.linha.value('@Serie','int') AS Serie,
(CASE WHEN Alunos.linha.value('@Apostolo','varchar(60)') = '1' THEN 'SIM' ELSE 'NÃO' END) AS Apostolo,
(CASE WHEN Alunos.linha.value('@Traidor','varchar(2)') = '1' THEN 'SIM' ELSE 'NÃO' END) AS Traidor
FROM
@XML.nodes('/Escola/Turma') Turmas(linha)
CROSS APPLY Turmas.linha.nodes('Aluno') Alunos(linha)
Utilizando XQuery (função XML.query)
Muitas vezes ao manusear arquivos XML, sentimos a necessidade de realizar queries ou filtros mais avançados para retornar a informação que desejamos ao invés de todo o arquivo. Para essa necessidade, temos a função query:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
@XML.query('.') AS XML_Completo,
@XML.query('Escola/Turma[1]/Aluno[1]') AS XML_Turma1_Aluno1,
@XML.query('Escola/Turma[1]/Aluno[1]/text()') AS XML_Turma1_Aluno1_Nome,
@XML.query('Escola/Turma[1]/Aluno[1]').value('.', 'varchar(100)') AS Turma1_Aluno1_Nome
Utilizando o XQuery para filtrar resultados:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
@XML.query('Escola/Turma[@Serie=''2'']') AS XML_Turma_2a_Serie,
@XML.query('Escola/Turma[@Nome=''Turma 1'']') AS XML_Turma1,
@XML.query('Escola/Turma/Aluno[@Traidor=''1'']') AS XML_Judas,
@XML.query('Escola/Turma/Aluno[@Apostolo=''0'']') AS XML_Paulo,
@XML.query('Escola/Turma/Aluno[.=''Pedro'']') AS XML_Pedro
Retornando as informações do aluno Pedro:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
DECLARE @XML2 XML = (SELECT @XML.query('Escola/Turma/Aluno[.=''Pedro'']'))
SELECT
Alunos.linhas.value('.', 'varchar(100)') AS Nome,
Alunos.linhas.value('@Nota', 'float') AS Nota,
Alunos.linhas.value('@Apostolo', 'bit') AS Apostolo,
Alunos.linhas.value('@Traidor', 'bit') AS Apostolo
FROM
@XML2.nodes('/Aluno') AS Alunos(linhas)
Trabalhando com valores:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
DECLARE @XML_Aprovados XML = (SELECT @XML.query('//Aluno[@Nota>=7]'))
DECLARE @XML_Reprovados XML = (SELECT @XML.query('//Aluno[@Nota<7]'))
SELECT
Alunos.linhas.value('.', 'varchar(100)') AS Nome,
Alunos.linhas.value('@Nota', 'float') AS Nota,
Alunos.linhas.value('@Apostolo', 'bit') AS Apostolo,
Alunos.linhas.value('@Traidor', 'bit') AS Apostolo
FROM
@XML_Aprovados.nodes('/Aluno') AS Alunos(linhas)
ORDER BY
Nota DESC
SELECT
Alunos.linhas.value('.', 'varchar(100)') AS Nome,
Alunos.linhas.value('@Nota', 'float') AS Nota,
Alunos.linhas.value('@Apostolo', 'bit') AS Apostolo,
Alunos.linhas.value('@Traidor', 'bit') AS Apostolo
FROM
@XML_Reprovados.nodes('/Aluno') AS Alunos(linhas)
ORDER BY
Nota
Uma coisa que se deve observar sobre a função query, é que ela não permite selecionar dados de atributos que estão no mesmo nível do nó atual. Exemplo, se você está utilizando um seletor de aluno, você não consegue retornar um atributo desse nó Aluno que você está selecionando. Caso você tente fazer isso, irá encontrar uma dessas mensagens de erro:
XQuery [Sqm.data.query()]: Attribute may not appear outside of an element
XQuery [value()]: Top-level attribute nodes are not supported
Para contornar isso, utilize a função value e aplique seus filtros de outra forma.
Utilizando funções no XML
Um outro recurso muito bacana do XML é a possibilidade de utilizar funções para filtrar ou obter mais informações através dos dados do nosso XML. Vou demonstrar agora como aplicar isso aos nossos arquivos XML de exemplo.
Utilizando funções numéricas:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
@XML.value('max(//Aluno/@Nota)', 'float') AS Maior_Nota,
@XML.value('min(//Aluno/@Nota)', 'float') AS Menor_Nota,
@XML.value('avg(//Aluno/@Nota)', 'float') AS Media_Nota,
@XML.value('sum(//Aluno/@Nota)', 'float') AS Soma_Nota,
@XML.value('count(//Aluno/@Nota)', 'int') AS Qtde_Nota,
@XML.value('count(//Aluno[@Nota>=7]/@Nota)', 'int') AS Qtde_Nota_Maior7,
@XML.value('count(//Aluno[@Nota<7]/@Nota)', 'int') AS Qtde_Nota_Menor7
Funções de string:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
@XML.value('(//Aluno[.=''Pedro''])[1]', 'varchar(100)') AS Pedro,
@XML.value('string-length((//Aluno[.=''Pedro''])[1])', 'varchar(100)') AS Tamanho_Nome_Pedro,
@XML.value('concat((//Aluno[.=''Pedro''])[1], '' Teste'')', 'varchar(100)') AS [Concat],
@XML.value('substring((//Aluno[.=''Pedro''])[1], 1, 1)', 'varchar(100)') AS [Primeira_Letra]
ou podemos utilizar o contains:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
Alunos.linha.value('.', 'varchar(100)') AS Pedro,
Alunos.linha.value('string-length((.))', 'varchar(100)') AS Tamanho_Nome_Pedro,
Alunos.linha.value('concat((.), '' Teste'')', 'varchar(100)') AS [Concat],
Alunos.linha.value('substring((.), 1, 1)', 'varchar(100)') AS [Primeira_Letra]
FROM
@XML.nodes('//Aluno') AS Alunos(linha)
WHERE
Alunos.linha.value('contains((.), "Pedro")', 'bit') = 1
e até mesmo operações simples de Transact-SQL:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT
Alunos.linha.value('.', 'varchar(100)') AS Pedro,
Alunos.linha.value('string-length((.))', 'varchar(100)') AS Tamanho_Nome_Pedro,
Alunos.linha.value('concat((.), '' Teste'')', 'varchar(100)') AS [Concat],
Alunos.linha.value('substring((.), 1, 1)', 'varchar(100)') AS [Primeira_Letra]
FROM
@XML.nodes('//Aluno') AS Alunos(linha)
WHERE
Alunos.linha.value('.', 'varchar(100)') = 'Pedro'
Manipulando o XML com a função modify
Quando estamos utilizando a função modify, temos mais uma prova do quão avançado é o tratamento de XML com o SQL Server. Essa função nos permite modificar os dados armazenados na variável XML em tempo de execução, e ela nos permite inserir dados (insert), substituir dados (replace value of) e apagar dados (delete).
Para a utilização da função modify(), deve-se realizar UPDATE, DELETE ou SET @Variavel. A utilização dessa função durante um SELECT não é permitido.
Como substituir valores utilizando a função replace value of:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT @XML.value('(//Aluno[.="Pedro"]/@Nota)[1]', 'float') AS Nota_Antes
------------------------------------------------------
-- ALTERA O VALOR DA NOTA DO ALUNO "PEDRO"
------------------------------------------------------
DECLARE @Tabela_XML TABLE ( Dados XML )
INSERT INTO @Tabela_XML
SELECT Ds_Xml FROM #XML2
UPDATE @Tabela_XML
SET Dados.modify('replace value of (//Aluno[.="Pedro"]/@Nota)[1] with("8.5")')
SET @XML = (SELECT TOP 1 Dados FROM @Tabela_XML)
SELECT @XML.value('(//Aluno[.="Pedro"]/@Nota)[1]', 'float') AS Nota_Depois
Remover um elemento do XML utilizando a função delete:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SELECT Alunos.linha.value('.', 'varchar(100)') AS Todos_os_Alunos
FROM @XML.nodes('//Aluno') Alunos(linha)
------------------------------------------------------
-- REMOVE OS ALUNOS COM NOTA MENOR QUE 7
------------------------------------------------------
DECLARE @Tabela_XML TABLE ( Dados XML )
INSERT INTO @Tabela_XML
SELECT Ds_Xml FROM #XML2
UPDATE @Tabela_XML
SET Dados.modify('delete (//Aluno[@Nota<7])')
SET @XML = (SELECT TOP 1 Dados FROM @Tabela_XML)
SELECT
Alunos.linha.value('.', 'varchar(100)') AS Alunos_Aprovados,
Alunos.linha.value('@Nota', 'float') AS Nota
FROM @XML.nodes('//Aluno') Alunos(linha)
Inserir nós no XML utilizando a função insert:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
SET @XML.modify('insert <Aluno Apostolo="0" Traidor="0" Nota="2.6">Dirceu Turma 1</Aluno>
into (//Turma)[1]')
SET @XML.modify('insert <Aluno Apostolo="0" Traidor="0" Nota="2.9">Dirceu Turma 2</Aluno>
into (//Turma)[2]')
SELECT
Alunos.linha.value('.', 'varchar(100)') AS Alunos_Aprovados,
Alunos.linha.value('@Nota', 'float') AS Nota
FROM @XML.nodes('//Aluno') Alunos(linha)
Inserir vários nós no XML a partir de uma variável SQL:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
DECLARE @Inserir XML = '
<Turma Nome="Turma 3" Serie="3">
<Aluno Apostolo="0" Traidor="0" Nota="2.6">Dirceu 1</Aluno>
<Aluno Apostolo="0" Traidor="0" Nota="2.6">Dirceu 2</Aluno>
</Turma>'
SET @XML.modify('insert sql:variable("@Inserir")
into (//Escola)[1]')
SELECT
Alunos.linha.value('.', 'varchar(100)') AS Alunos,
Alunos.linha.value('@Nota', 'float') AS Nota,
Alunos.linha.value('../@Nome', 'varchar(100)') AS Turma
FROM
@XML.nodes('//Aluno') Alunos(linha)
Utilizando expressões FLOWR do XQuery no XML
O recurso FLOWR (se pronuncia “flower”) é uma poderosa ferramenta e extensão da função QUERY, que permite realizar iterações em arquivos XML e realizar uma série de operações. Os comandos são FOR, LET, ORDER BY, WHERE e RETURN. Vamos ver abaixo como utilizá-los.
Utilizando a iteração FOR:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
DECLARE @XML2 XML = (SELECT @XML.query('for $A in //Aluno[@Nota>=7] return $A'))
SELECT
Alunos.linha.value('.', 'varchar(100)') AS Nome,
Alunos.linha.value('@Nota', 'float') AS Nota
FROM
@XML2.nodes('//Aluno') Alunos(linha)
Utilizando a iteração FOR, WHERE e ORDER BY:
DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2)
DECLARE @XML2 XML = (SELECT @XML.query('
for $A
in //Aluno
where ($A = "Pedro" or $A = "Marcos" or $A = "Andre" or $A = "Joao")
order by $A descending
return $A'))
SELECT
Alunos.linha.value('.', 'varchar(100)') AS Nome,
Alunos.linha.value('@Nota', 'float') AS Nota
FROM
@XML2.nodes('//Aluno') Alunos(linha)
Utilizando o comando LET para alterar o valor de uma informação e dar um aumento de 20% para dois funcionários:
Nesse exemplo específico, eu crio o XML em tempo real, pois os meus 2 XML de exemplo não possuíam valores em dados, apenas em atributos, o que impossibilita a utilização junto com a função query.
DECLARE @XML XML = '
<Empresa>
<Funcionario>
<Nome>Joao</Nome>
<Salario>1250.28</Salario>
</Funcionario>
<Funcionario>
<Nome>Pedro</Nome>
<Salario>1754.54</Salario>
</Funcionario>
<Funcionario>
<Nome>Paulo</Nome>
<Salario>5487.99</Salario>
</Funcionario>
</Empresa>'
DECLARE @XML_Modificado XML = (SELECT @XML.query('
for $A
in //Funcionario
let $T := 1.20
where $A/Nome != "Pedro"
return
<Funcionario>
<Nome>{data($A/Nome)}</Nome>
<NovoSalario>{data($A/Salario/text())[1] * data($T)}</NovoSalario>
</Funcionario>'))
SELECT
Funcionarios.linha.value('Nome[1]', 'varchar(100)') AS Nome,
Funcionarios.linha.value('NovoSalario[1]', 'numeric(18,2)') AS NovoSalario
FROM
@XML_Modificado.nodes('//Funcionario') Funcionarios(linha)
Exportando dados do banco para XML – FOR XML RAW, AUTO, EXPLICIT, PATH
Depois de muito se falar sobre importação e tratamento de arquivos XML, finalmente chegou a hora de fazer o caminho inverso. Como transformar dados de uma tabela do SQL Server para uma string XML.
Para facilitar essa tarefa, o SQL Server disponibiliza 4 formas de se fazer isso com o FOR XML: RAW, AUTO, EXPLICI e PATH. Vamos descobrir agora para que serve e como utilizá-los.
Criação da tabela de testes:
IF (OBJECT_ID('dbo.Teste_XML') IS NOT NULL) DROP TABLE dbo.Teste_XML
CREATE TABLE dbo.Teste_XML (
Categoria VARCHAR(100),
Descricao VARCHAR(100)
)
INSERT INTO dbo.Teste_XML ( Categoria, Descricao )
VALUES ('Brinquedo', 'Bola'),
('Brinquedo', 'Carrinho'),
('Brinquedo', 'Boneco'),
('Brinquedo', 'Jogo'),
('Cama e Mesa', 'Toalha'),
('Cama e Mesa', 'Edredom'),
('Informatica', 'Teclado'),
('Informatica', 'Mouse'),
('Informatica', 'HD'),
('Informatica', 'CPU'),
('Informatica', 'Memoria'),
('Informatica', 'Placa-Mae'),
(NULL, 'TV')
FOR XML RAW
O método RAW do FOR XML gera um XML da nossa tabela onde cada coluna se transforma em atributo do XML gerado e cada linha irá representar um nó (elemento).
SELECT *
FROM dbo.Teste_XML
FOR XML RAW
XML gerado:
<row Categoria="Brinquedo" Descricao="Bola" />
<row Categoria="Brinquedo" Descricao="Carrinho" />
<row Categoria="Brinquedo" Descricao="Boneco" />
<row Categoria="Brinquedo" Descricao="Jogo" />
<row Categoria="Cama e Mesa" Descricao="Toalha" />
<row Categoria="Cama e Mesa" Descricao="Edredom" />
<row Categoria="Informatica" Descricao="Teclado" />
<row Categoria="Informatica" Descricao="Mouse" />
<row Categoria="Informatica" Descricao="HD" />
<row Categoria="Informatica" Descricao="CPU" />
<row Categoria="Informatica" Descricao="Memoria" />
<row Categoria="Informatica" Descricao="Placa-Mae" />
<row Descricao="TV" />
No exemplo abaixo, vou acrescer a opção ROOT após o RAW(), para adicionar um elemento raiz que irá ser o nó pai dos nós criados. Isso é opcional, se você não utilizar, a única diferença é que o elemento <root> não será criado.
SELECT *
FROM dbo.Teste_XML
FOR XML RAW('Produto'), ROOT('Produtos')
XML gerado:
<Produtos>
<Produto Categoria="Brinquedo" Descricao="Bola" />
<Produto Categoria="Brinquedo" Descricao="Carrinho" />
<Produto Categoria="Brinquedo" Descricao="Boneco" />
<Produto Categoria="Brinquedo" Descricao="Jogo" />
<Produto Categoria="Cama e Mesa" Descricao="Toalha" />
<Produto Categoria="Cama e Mesa" Descricao="Edredom" />
<Produto Categoria="Informatica" Descricao="Teclado" />
<Produto Categoria="Informatica" Descricao="Mouse" />
<Produto Categoria="Informatica" Descricao="HD" />
<Produto Categoria="Informatica" Descricao="CPU" />
<Produto Categoria="Informatica" Descricao="Memoria" />
<Produto Categoria="Informatica" Descricao="Placa-Mae" />
<Produto Descricao="TV" />
</Produtos>
Mesmo no XML RAW podemos fazer com que as colunas sejam retornadas como elementos do XML. Para isso, basta incluir a opção ELEMENTS:
SELECT *
FROM dbo.Teste_XML
FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS
XML gerado (apenas um trecho para não ficar muito grande):
<Produtos>
<Produto>
<Categoria>Brinquedo</Categoria>
<Descricao>Bola</Descricao>
</Produto>
<Produto>
<Categoria>Brinquedo</Categoria>
<Descricao>Carrinho</Descricao>
</Produto>
<Produto>
<Categoria>Brinquedo</Categoria>
<Descricao>Boneco</Descricao>
</Produto>
</Produtos>
Uma outra opção interessante do FOR XML é quando tratamos de dados vazios (NULL). Quando não realizamos nenhum tratamento, eles simplesmente são ignorados e não são gerados, como é o caso da categoria do produto “TV”. Para tratar isso, podemos utilizar a opção XSINIL após a opção ELEMENTS, que vai adicionar o elemento vazio e criar um atributo informando isso (xsi:nil=”true”):
SELECT *
FROM dbo.Teste_XML
FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS XSINIL
E aí o XML com o produto é gerado assim:
<Produto>
<Categoria xsi:nil="true" />
<Descricao>TV</Descricao>
</Produto>
Além disso, podemos utilizar a opção XMLSCHEMA para transformar nosso XML em um XSD completo:
SELECT *
FROM dbo.Teste_XML
FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS XSINIL, XMLSCHEMA
FOR XML AUTO
O modo AUTO do FOR XML é bem parecido com o RAW, mas com a diferença que na sua utilização padrão, o nome da tabela é o nome padrão de cada elemento.
SELECT *
FROM dbo.Teste_XML
FOR XML AUTO
XML gerado:
<dbo.Teste_XML Categoria="Brinquedo" Descricao="Bola" />
<dbo.Teste_XML Categoria="Brinquedo" Descricao="Carrinho" />
<dbo.Teste_XML Categoria="Brinquedo" Descricao="Boneco" />
<dbo.Teste_XML Categoria="Brinquedo" Descricao="Jogo" />
<dbo.Teste_XML Categoria="Cama e Mesa" Descricao="Toalha" />
<dbo.Teste_XML Categoria="Cama e Mesa" Descricao="Edredom" />
<dbo.Teste_XML Categoria="Informatica" Descricao="Teclado" />
<dbo.Teste_XML Categoria="Informatica" Descricao="Mouse" />
<dbo.Teste_XML Categoria="Informatica" Descricao="HD" />
<dbo.Teste_XML Categoria="Informatica" Descricao="CPU" />
<dbo.Teste_XML Categoria="Informatica" Descricao="Memoria" />
<dbo.Teste_XML Categoria="Informatica" Descricao="Placa-Mae" />
<dbo.Teste_XML Descricao="TV" />
FOR XML PATH
O XML PATH é um pouco diferente dos outros dois exemplos, pois o nome de colunas e aliases são tratados como elementos XPATH. Quando você gera um XML comum, sem personalizar, ele inclui um elemento raiz (row), onde cada linha é um elemento filho, um nível de hierarquia abaixo e cada coluna é também um elemento do XML, mais outro nível abaixo:
Assim como o XML AUTO e XML RAW, podemos utilizar ROOT(‘NomeDaRaiz’) para criar o elemento raiz, podemos utilizar também a opção ELEMENTS com XSINIL também (para retornar elementos mesmo nulos).
Obs: Como o XML PATH sempre retorna as colunas como elementos, utilizar apenas a opção ELEMENTS não irá fazer efeito, apenas se utilizada em conjunto com a XSINIL.
Criando hierarquias com o XML PATH:
SELECT
Id AS '@Id_Produto', -- Atributo
Categoria AS 'DadosProduto/Categoria', -- Elemento
Descricao AS 'DadosProduto/Descricao'-- Elemento
FROM
dbo.Teste_XML
FOR XML PATH('Produto'), ROOT('Produtos'), ELEMENTS
XML gerado:
FOR XML EXPLICIT
O modo EXPLICIT do FOR XML já tende a ser bem diferente dos outros modos. Isso ocorre porque ele exige um cabeçalho em um formato específico, definindo hierarquia e estruturas. Esse cabeçalho deve ter unido aos dados utilizando UNION ALL.
O SELECT do cabeçalho deve possuir a seguinte estrutura:
- Primeira coluna: É um número que define o nível da hierarqua. O nome da coluna obrigatoriamente deve ser Tag.
- Segunda coluna: É um número que define o nível da hierarqua do elemento pai (ou NULL, caso não tenha e seja a raiz). O nome da coluna obrigatoriamente deve ser Parent.
- Terceira coluna em diante: Sâo os dados que farão parte do XML e serão retornados pelo seu XML.
Note que precisamos desde o início definir todas as colunas que farão parte do XML já no cabeçalho.
O formato padrão para definição de campo é definido da seguinte forma:
<NomeDoElemento>!<NúmeroDaTag>!<NomeDoAtributo>[!<InformacoesAdicionais>]
Onde:
- NomeDoElemento: É o nome do elemento pai que estamos gerando (No caso do exemplo, Produtos)
- NúmeroDaTag: É o número do nível da hierarquia dos elementos filhos
- NomeDoAtributo: É o nome de cada atributo/coluna dos dados que estamos exportando para XML (no caso do exemplo, Categoria e Descricao)
- InformacoesAdicionais: Dados adicionais que podem ser utilizados na construção do XML
Vamos ver agora como isso funciona na prática:
-- Cabeçalho
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS [Produtos!1!Id],
NULL AS [Produto!2!Categoria!ELEMENT],
NULL AS [Produto!2!Descricao!ELEMENT]
UNION ALL
-- Conteúdo
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Categoria,
Descricao
FROM
dbo.Teste_XML
FOR
XML EXPLICIT
Trecho do XML retornado:
Pro exemplo ficar mais legal, vou adicionar uma nova coluna na nossa tabela:
ALTER TABLE dbo.Teste_XML ADD Id INT IDENTITY(1,1)
Gerando novamente o XML:
-- Cabeçalho
SELECT
1 AS Tag,
NULL AS Parent,
Id AS [Produtos!1!Id_Produto],
NULL AS [Produto!2!Categoria!ELEMENT],
NULL AS [Produto!2!Descricao!ELEMENT]
FROM
dbo.Teste_XML
UNION ALL
-- Conteúdo
SELECT
2 AS Tag,
1 AS Parent,
Id AS Id_Produto,
ISNULL(Categoria, ''),
Descricao
FROM
dbo.Teste_XML
ORDER BY
[Produtos!1!Id_Produto],
[Produto!2!Categoria!ELEMENT]
FOR
XML EXPLICIT
Nosso resultado será este:
Vale ressaltar que esse ORDER BY utilizado é necessário para que os resultados sejam exibidos na forma correta. Caso contrário, os elementos serão gerados na ordem errada e o XML não terá o mesmo resultado.
Como podemos observar, o novo campo adicionado (ID) é um atributo do XML. Caso você queira transformá-lo em um atributo, basta adicionar !ELEMENT no seu cabeçalho:
-- Cabeçalho
SELECT
1 AS Tag,
NULL AS Parent,
Id AS [Produtos!1!Id_Produto!ELEMENT],
NULL AS [Produto!2!Categoria!ELEMENT],
NULL AS [Produto!2!Descricao!ELEMENT]
FROM
dbo.Teste_XML
UNION ALL
-- Conteúdo
SELECT
2 AS Tag,
1 AS Parent,
Id AS Id_Produto,
ISNULL(Categoria, ''),
Descricao
FROM
dbo.Teste_XML
ORDER BY
[Produtos!1!Id_Produto!ELEMENT],
[Produto!2!Categoria!ELEMENT]
FOR
XML EXPLICIT
Ficando dessa forma:
Nos dois exemplos acima, eu adicionei uma cláusula ISNULL() na categoria, uma vez que o produto “TV” não tem categoria definida. Quando isso ocorre no modo EXPLICIT, e a categoria é utilizada para ordenar os resultados, os elementos acabam se perdendo e os sem categoria ficam juntos com elementos de outras categorias.
Uma outra forma de resolver este problema, além de adicionar os ISNULL(), é definindo o tipo de atributo ELEMENTXSINIL no cabeçalho da estrutura do nosso SELECT e não utilizando essa coluna que tem possíveis valores NULL no ORDER BY:
-- Cabeçalho
SELECT
1 AS Tag,
NULL AS Parent,
Id AS [Produtos!1!Id_Produto!ELEMENT],
NULL AS [Produto!2!Categoria!ELEMENTXSINIL],
NULL AS [Produto!2!Descricao!ELEMENT]
FROM
dbo.Teste_XML
UNION ALL
-- Conteúdo
SELECT
2 AS Tag,
1 AS Parent,
Id AS Id_Produto,
Categoria,
Descricao
FROM
dbo.Teste_XML
ORDER BY
[Produtos!1!Id_Produto!ELEMENT],
[Produto!2!Descricao!ELEMENT]
FOR
XML EXPLICIT
XML gerado:
Caso você tenha alguma dúvida ou sugestão, deixe aqui nos comentários.
Obrigado pela visita e até a próxima!
sql server como aprender trabalhar usar ler importar tratar xml string arquivo learning
sql server como aprender trabalhar usar ler importar tratar xml string arquivo learning
Comentários (0)
Carregando comentários…