Exportando consultas para documentos XML

Boa Noite Pessoal,

Essa será possivelmente uma das minhas últimas postagens de 2011, então vamos para uma dica rápida. Hoje falarei sobre como exportar uma tabela ou consulta para um documento XML através do BCP e do CLR. Já vi algumas postagens que tratam esse assunto, mas proponho um pouco mais que isso. Demonstrarei a exportação mantendo a identação dos elementos no documento XML. Para começar nada melhor que algumas tabelas de exemplo

— Cria um banco para testes
CREATE DATABASE ExportXML
GO

— Muda o contexto
USE ExportXML
GO

— Cria as tabelas
CREATE TABLE Clientes (
    ClienteID INT NOT NULL,
    ClienteNome VARCHAR(100) NOT NULL)

CREATE TABLE Contas (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    ContaAgencia VARCHAR(6) NOT NULL,
    ContaNumero VARCHAR(9) NOT NULL)

CREATE TABLE Lancamentos (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    Valor SMALLMONEY NOT NULL,
    Tipo CHAR(1) NOT NULL,
    Data DATE NOT NULL)

— Insere os registros
INSERT INTO Clientes VALUES (1,‘Ronaldo’)
INSERT INTO Clientes VALUES (2,‘Adalton’)
INSERT INTO Clientes VALUES (3,‘Daniela’)
INSERT INTO Clientes VALUES (4,‘Roberta’)

INSERT INTO Contas VALUES (1,1,‘3590-4’,‘97532-8’)
INSERT INTO Contas VALUES (1,2,‘3590-4’,‘856413-0’)
INSERT INTO Contas VALUES (1,3,‘3590-4’,‘948766-0’)
INSERT INTO Contas VALUES (2,1,‘1004-9’,‘974322-8’)
INSERT INTO Contas VALUES (2,2,‘1004-9’,‘15649-X’)
INSERT INTO Contas VALUES (3,1,‘2944-0’,‘7561-2’)

INSERT INTO Lancamentos VALUES (1,1,6500,‘C’,‘20111220’)
INSERT INTO Lancamentos VALUES (1,1,1200,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,1,2500,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,3500,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,2600,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (2,1,3200,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1100,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1900,‘C’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,2,5800,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,3200,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,9800,‘C’,‘20111223’)

Agora que temos uma massa de testes, segue um consulta para estruturar esses dados em XML.

SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Posto apenas um parte do documento XML produzido com esse comando:

<Lancamentos>
  <Cliente Nome=Ronaldo>
    <Conta Agencia=3590-4Numero=97532-8>
      <Lancamento Data=2011-12-20Valor=6500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=1200.0000Tipo=D/>
      <Lancamento Data=2011-12-21Valor=2500.0000Tipo=D/>
    </Conta>
    <Conta Agencia=3590-4Numero=856413-0“>
      <Lancamento Data=2011-12-21Valor=3500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=2600.0000Tipo=C/>
    </Conta>
    <Conta Agencia=3590-4Numero=948766-0>
      <Lancamento />
    </Conta>
  </Cliente>
  <Cliente Nome=Adalton>
    <Conta Agencia=1004-9Numero=974322-8>

Para facilitar os trabalhos, vou encapsultar a instrução SELECT em uma View.

CREATE VIEW vLancamentoXML (ResultadoXML) As
SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Agora façamos uma tentativa de exporar esse conteúdo para um arquivo através do BCP. A instrução abaixo em um PROMPT de comando é capaz de fazê-lo.

BCP “ExportXML.dbo.vLancamentoXML” out “C:\Lancamentos.xml” -T -c

Eis uma parte do resultado do arquivo em XML

<Lancamentos><Cliente Nome=”Ronaldo”><Conta Agencia=”3590-4″ Numero=”97532-8″><Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>

O documento XML foi criado e pode ser plenamente utilizado, pois, é um XML bem formado, mas a identação foi completamente perdida na geração do documento. Se esse XML for processado por uma aplicação, não faz diferença ele estar identado ou não, mas se uma pessoa for abrí-lo, a falta de identação pode ser um grande inconviente.

Uma vez que o BCP foi projetado para exportar texto e não XML, não há parâmetros que possam fazer a identação ficar correta. Então precisaremos dar um jeito do texto já ser exportado de forma identada. Criei aqui uma função capaz de fazê-lo.

CREATE FUNCTION dbo.RetornaXMLGRID (@XML XML)
RETURNS @Registros TABLE (Registro VARCHAR(MAX))
As
BEGIN
    DECLARE
@strXML VARCHAR(MAX)
    SET @strXML = CAST(@XML As VARCHAR(MAX))
    SET @strXML = REPLACE(@strXML,‘<‘,‘+|’)
    SET @strXML = REPLACE(@strXML,‘<‘,‘-|’)

    DECLARE @T TABLE (
        Pos INT IDENTITY(1,1), Elemento VARCHAR(1000))

    INSERT INTO @T SELECT
        node.value(N’fn:local-name(.[1])’, N’varchar(1000)’)
    FROM @xml.nodes(N’//*’) T(Node)

    ;WITH Niveis (Elemento, Nivel) As (
        SELECT Elemento, MIN(Pos)
        FROM @T GROUP BY Elemento)

    DELETE FROM @T
    FROM @T As T
    INNER JOIN Niveis As N ON
        T.Elemento = N.Elemento AND T.Pos > N.Nivel

    DECLARE @i INT = 0
    DECLARE @f INT = (SELECT MAX(Pos) FROM @T)
    DECLARE @Elemento VARCHAR(1000) =

    WHILE @i <= @f – 1
    BEGIN
        SET
@Elemento = (
            SELECT Elemento FROM @T WHERE Pos = @i + 1)

        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento +
‘ ‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘ ‘)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘-|’)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘/-|‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘/-|’)
        SET @strXML = REPLACE(@strXML,‘+|/’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|/’ + @Elemento + ‘-|’)
        SET @i += 1
    END
   
    SET @strXML = REPLACE(@strxml,CHAR(10),‘</e><e>’)
    SET @strXML = ‘<E><e>’ + @strXML + ‘</e></E>’
   
    SET @XML = CAST(@strXML As XML)
   
    INSERT INTO @Registros
    SELECT REPLACE(REPLACE(E.e.value(‘.’,‘VARCHAR(MAX)’),‘+|’,‘<‘),‘-|’,‘>’)
    FROM @XML.nodes(‘/E/e’) E(e)
   
    DELETE FROM @Registros WHERE Registro =
   
    RETURN
END

Agora tentemos o BCP novamente com algumas adaptações:

BCP “SELECT * FROM dbo.RetornaXMLGRID((SELECT ResultadoXML FROM vLancamentoXML))” queryout “C:\Lancamentos.xml” -T -c -d ExportXML

O comando SELECT ResultaadoXML FROM vLancamentoXML é apenas para retornar um texto (ou um tipo XML) no formato de um documento XML. É possível substituir essa parte do comando por qualquer consulta XML. O documento agora veio formatado após abrir o arquivo XML:

<Lancamentos>
<Cliente Nome=”Ronaldo”>
  <Conta Agencia=”3590-4″ Numero=”97532-8″>
   <Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”1200.0000″ Tipo=”D”/>
   <Lancamento Data=”2011-12-21″ Valor=”2500.0000″ Tipo=”D”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”856413-0″>
   <Lancamento Data=”2011-12-21″ Valor=”3500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”2600.0000″ Tipo=”C”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”948766-0″>
   <Lancamento/>
  </Conta>
</Cliente>
<Cliente Nome=”Adalton”>
  <Conta Agencia=”1004-9″ Numero=”974322-8″>

Um teste para garantir que o XML só precisa ser váido por ser feito com outra consulta:

SELECT * FROM dbo.RetornaXMLGRID((
SELECT db.name As banco, t.name As tabela, c.name As coluna FROM sys.databases As db INNER JOIN sys.tables As t ON db.database_id = db_id() INNER JOIN sys.columns As c ON t.object_id = c.object_id for xml auto, elements))

Parte do resultado é exibido abaixo:

<db>
<banco>ExportXML
</banco>
  <t>
   <tabela>Clientes
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>
    </c>
    <c>
     <coluna>ClienteNome
     </coluna>
    </c>
  </t>
  <t>
   <tabela>Contas
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>

Acho que já são visíveis algumas limitações desse método. Primeiro porque com tantas conversões, essa alternativa não será escalável quando houver documentos XML muito grandes. Segundo, porque se houver elementos com o mesmo nome pertencentes a níveis diferentes, essa função também irá falhar. Isso torna claro que o T-SQL é poderoso, mas não foi feito para resolver todos os problemas. Tentemos uma abordagem com o CLR para esse mesmo fim.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void UspGerarArquivoXML(SqlString cmdSQL, SqlString local)
    {
        SqlConnection sc = new SqlConnection(“Context Connection=true”);
        SqlCommand sCmd = new SqlCommand(cmdSQL.ToString(), sc);

        // Abre a conexão
        sc.Open();

        // Declara um objeto XmlReader
        XmlReader xr = sCmd.ExecuteXmlReader();

        // Fecha a conexão
        sc.Close();

        // Declara um documento XML
        XmlDocument xd = new XmlDocument();

        // Inicia o documento
        xd.Load(xr);

        // Salva o documento
        xd.Save(local.ToString());
    }
};

Agora que o código em C# está pronto, basta colocá-lo em uma pasta qualquer (C:\SQLCLR\UspGeraArquivoXML.cs) e compilá-lo com o comando de PROMPT

“C:\Windows\Microsoft.NET\Framework\V2.0.50727\csc.exe” /out:C:\SQLCLR\UspGeraArquivoXML.dll /target:library C:\SQLCLR\UspGeraArquivoXML.cs

Os próximos passos são habilitar o CLR e importar o ASSEMBLY:

EXEC sp_configure ‘clr enabled’,1
GO

RECONFIGURE WITH OVERRIDE
GO

USE ExportXML
GO

ALTER DATABASE ExportXML SET TRUSTWORTHY ON

CREATE ASSEMBLY ExportarArquivosXML
FROM ‘C:\SQLCLR\UspGeraArquivoXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Agora que o ASSEMBLY foi gerado, basta criar a procedure e chamá-la:

— Cria a stored procedure
CREATE PROCEDURE UspGerarArquivoXML
    @cmdSQL NVARCHAR(4000),
    @local NVARCHAR(4000)
As
EXTERNAL
NAME [ExportarArquivosXML].[StoredProcedures].[UspGerarArquivoXML]
GO

Por fim, podemos executar a SP com a consulta e o local do arquivo (é importante que a consulta tenha o nó ROOT e que o local tenha as devidas permissões).

— Exporta o Resultado para XML
EXEC dbo.UspGerarArquivoXML
    @cmdSQL =
    SELECT
        Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
        Conta.ContaNumero As Numero,Lancamento.Data As Data,
        Lancamento.Valor, Lancamento.Tipo
    FROM Clientes As Cliente
        LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
        LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
        FOR XML AUTO, ROOT(”Lancamentos”)’
,
    @local = ‘C:\SQLCLR\LancamentosCLR.xml’

O documento já vem identado, mas visivelmente o esforço é muito menor do que escrever código T-SQL para isso.

[ ]s,

Gustavo

8 Respostas para “Exportando consultas para documentos XML

  1. Leonardo Cristiano de Alice

    Boa dica Gustavo, parabéns pelo seu blog, é fantástico!
    Aproveitando o comentário, quero lhe perguntar se você pode me indicar algum artigo ou fonte na qual trabalha a montagem do WHERE em um script. Eu tenho algumas dúvidas como qual ordem de filtro seguir, qual filtro devo por no WHERE e qual devo por nas ligações (LEFT, RIGHT, INNER…)
    Desde já agradeço e, mais uma vez, o parabenizo pelo trabalho!

  2. Diego Miranda

    Gustavo, boa tarde.

    Eu tenho uma dúvida que creio que você poderia me elucidar.

    Estamos efetuando alguns testes com o SQLServer 2008 utilizando SnapShot. A pesquisa de registros utilizando registros que estão no Snapshot é bem mais lenta do que a leitura suja em si.

    Qual seria a razão?

    • Oi Diego,

      Embora nenhum dos dois fique bloqueado, o Snapshot tem de gerar as versões de linha e o consumo em cima do TempDB aumenta. Essa é a causa provável da lentidão. O NOLOCK não tem esse ônus, mas a consistência é fraca.

      [ ]s,

      Gustavo

  3. Gustavo boa tarde,
    já fiz várias consultas aqui em seu blog que me ajudaram a chegar nos resultados que precisei, aqui existe muito conteúdo de fácil entendimento.
    Lendo esse seu post sobre o XML, me deparei com a situação inversa aqui, tenho os XML’s mas preciso “quebrar” estes dados em uma view? Pode me dar alguma sugestão
    Grande abraço

  4. Olá, Gustavo.
    Estou com os dados de uma pesquisa que a ANS solicitou aos planos de saúde, e preciso colocar no formato XML. Você oferece este serviço?

    • Olá Gilson,

      Já faz algum tempo e creio que você já deva ter solucionado a sua questão. Passar dados não estruturados para XML é fácil fácil e com os artigos que já postei eu creio que você não terá dificuldades. Entretanto, se ainda estiver precisando dos meus serviços entre em contato que eu retorno ok ?

      [ ]s,

      Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s