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-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“>
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
