Compartilhar via

Exportando uma consulta T-SQL para um arquivo XML

  • Artigo

Introdução

Este artigo apresenta um procedimento para exportar os dados de uma consulta através de Transact-SQL(T-SQL) utilizando uma combinação de stored procedures com um utilitário do SQL Server, desde que seu usuário tenha acesso para extrair a informação e manipular o arquivo.

Habilitando Configurações do SQL Server

Estas atualização de parâmetros em uma instância SQL, possibilitam a utilização de recursos para manipulação de comandos Shell do Windows, através de instruções comuns de Prompt de Comando para execução na plataforma Windows

EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO
 
EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE GO

Os processos executados no Windows à partir da stored procedure de sistema xp_cmdshell tem os mesmos direitos de segurança que a conta utilizada pelo serviço do SQL Server desta instância.

Exportando os Dados

Para manipulação dos dados, utilizamos uma consulta T-SQL utilizando os recursos definidos através da cláusula FOR XML. Isto possibilita diversas possibilidades de formatação de estrutura de dados, o que pode ajudar a atender diferentes demandas de integração de informações entre Plataformas de dados diferentes.

Segue abaixo a consulta que será utilizada nesta demonstração:

SELECT * from SuaTabela AS LINHA FOR XML auto, root('TESTEXML'), elements;

Com a utilização do modo "auto" na cláusula FOR XML, podemos gerar uma hierarquia XML enxuta e mais simplificada, com elementos XML aninhados.

Para definir as colunas indicadas na instrução SELECT mapeadas como atributos ou elementos, utilizamos a condição ELEMENTS na cláusula FOR XML., enquanto o ambiente não está plenamente reestabelecido, é possível utilizar os bancos de dados de usuários em outra Instância SQL Server.

O XML obtido é baseado na ordem indicada pelas colunas especificadas na instrução SELECT e do seu resultado conforme indexado ou reorganizado através da cláusula ORDER BY.

Para exportar o resultado desta instrução SELECT, podemos utilizar a combinação do utilitário BCP acionada através da stored procedure de sistema "xp_cmdshell".

Com intuito de facilitar a construção do comando a ser executado, foi criada neste exemplo uma variável chamada "@cmd" que possibilita alterar e ajustar a consulta e os parâmetros do BCP para extrair os dados ao arquivo de destino.

Segue abaixo o script T-SQL para exportar o resultado da consulta para o arquivo XML:

DECLARE @cmd varchar(255); 
SELECT @cmd = 'BCP "
SELECT * FROM SeuBanco.dbo.SuaTabela LINHA for xml auto, root(''TESTEXML''), elements" ' +  'queryout "c:\arquivo.xml" -S SeuServidor\SuaInstanciaSQL -T -w -r -t';
 EXEC xp_cmdshell @cmd;  GO

Se o usuário SQL utilizado possui permissão para executar os recursos do utilitário BCP, então você poderá manipular exportação destes dados.

Veja abaixo como este script T-SQL é executado e seu resultado, através do IDE do SQL Server Management Studio(SSMS):

https://social.technet.microsoft.com/Forums/getfile/663842

Este mesmo script T-SQL poderá ser utilizado em um Job ou através de outro comando para ser executado através da stored procedure de sistema "xp_cmdshell".

Veja abaixo o resultado deste script T-SQL armazenado no arquivo "Arquivo.xml" no diretório raiz do Windows. Preferencialmente, utilize um diretório específico para esta finalidade, com as restrições de segurança adequadas ao seu negócio.

O resultado obtido será uma estrutura XML, semelhante à imagem abaixo:

https://social.technet.microsoft.com/Forums/getfile/663844

Criando um arquivo Exclusivo

Como estamos gerando arquivos que devem ser processados por outros sistema e em outras plataformas, é recomendado manter (ao menos temporariamente) uma referência de data e hora de criação deste arquivo para caso seja necessário executar um reprocessamento destes dados na origem ou no destino.

Deste modo, você poderá personalizar seu script T-SQL, evitando que o arquivo seja sobrescrito, causando perda de dados.

Segue abaixo a adaptação no nome do arquivo para incluir a data e hora deste arquivo:

DECLARE @cmd varchar(255); DECLARE  @nome varchar(20) = REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 126), '-',''), ':',''), '.',''); 
SELECT @cmd = 'BCP "SELECT * FROM SeuBanco.dbo.SuaTabela LINHA for xml auto, root(''TESTEXML''), elements" ' +  'queryout "c:\arquivo' + @nome +'.xml" -S SeuServidor\SuaInstanciaSQL -T -w -r -t';

Conclusão

Podemos entender que a criação de um arquivo XML, com uma estrutura de dados simples, pode ser gerado através de T-SQL com certa facilidade, desde que o usuário tenha às permissões necessárias para manipular os dados e gerar este novo arquivo XML.

Este é um recurso muito útil em rotinas de integração de dados entre sistemas, principalmente, em diferentes plataformas que podem automatizar processos importantes para diversas soluções, graças ao formato padrão XML.

Referências

Veja Também