SQL Server – Como ler, importar e exportar dados de arquivos XML

Esses serãos os arquivos XML que vou importar para o banco de dados e transformá-los em tabelas para facilitar a manipulação das informações:

Base 1 (Informações como atributos)

Base 2 (Informações como conteúdo)

Vamos conferir agora, de forma fácil e prática, como fazer isso.

Como importar arquivos XML para o SQL Server

O primeiro passo para conseguirmos validar e trabalhar com os dados contidos em arquivos XML é importar esses dados para o nosso banco SQL Server. Caso você já tenha os dados do XML em uma tabela, pode ignorar esse passo.

Para a importação do XML a partir de um arquivo físico no disco ou em um diretório compartilhado da sua rede, vou utilizar a função fncArquivo_Ler_Retorna_String, do CLR (C#), por ser a mais fácil e prática de se utilizar.

Como eu já havia demonstrado no post SQL Server – Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT), inclusive com o código-fonte da função fncArquivo_Ler_Retorna_String, você também pode utilizar outras formas de importação de arquivos texto, conforme sua vontade ou restrição na instância.

Para realizar a importação do arquivo, podemos fazer da seguinte forma:

Como 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:

Agora que conseguimos carregar nosso XML para uma variável, vamos iniciar o tratamento dos dados.

Leitura simples:

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:

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:

Utilizando exist junto com o value:

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

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

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:

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

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:

Exemplo mais completo, misturando dados e atributos:

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:

Utilizando o XQuery para filtrar resultados:

Retornando as informações do aluno Pedro:

Trabalhando com valores:

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:

Funções de string:

ou podemos utilizar o contains:

e até mesmo operações simples de Transact-SQL:

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:

Remover um elemento do XML utilizando a função delete:

Inserir nós no XML utilizando a função insert:

Inserir vários nós no XML a partir de uma variável SQL:

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:

Utilizando a iteração FOR, WHERE e ORDER BY:

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.

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:

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).

XML gerado:

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.

XML gerado:

Mesmo no XML RAW podemos fazer com que as colunas sejam retornadas como elementos do XML. Para isso, basta incluir a opção ELEMENTS:

XML gerado (apenas um trecho para não ficar muito grande):

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”):

E aí o XML com o produto é gerado assim:

Além disso, podemos utilizar a opção XMLSCHEMA para transformar nosso XML em um XSD completo:

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.

XML gerado:

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:

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:

XHTML

<NomeDoElemento>!<NúmeroDaTag>!<NomeDoAtributo>[!<InformacoesAdicionais>]

1	<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:

Trecho do XML retornado:

Pro exemplo ficar mais legal, vou adicionar uma nova coluna na nossa tabela:

Gerando novamente o XML:

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:

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:

XML gerado: