SQL Server - Como exportar o conteúdo de uma coluna para arquivos XML

Visualizações: 2.917 views

Tempo de Leitura: 4 minutos

Fala galera!!
Na quinta-feira (09/04), vi uma dúvida bem legal no grupo do Telegram SQL Server – DBA, o maior grupo de DBA’s e Desenvolvedores SQL Server do mundo, onde a pessoa tinha uma tabela no banco, onde uma coluna era do tipo XML, e ele gostaria de exportar para o disco, cada linha dessa tabela como um arquivo XML separado e achei que daria um artigo legal demonstrar essa solução.

Para exportar os dados da tabela para arquivos texto no disco, vou utilizar as soluções compartilhadas no artigo SQL Server – Como exportar dados do banco para arquivo texto (CLR, OLE, BCP).

Caso você tenha interesse em aprender melhor a manipular e tratar arquivos XML dentro do SQL Server, sugiro a leitura do artigo SQL Server – Como ler, importar e exportar dados de arquivos XML.

Para esse exemplo, vou utilizar o script abaixo para criar uma tabela de demonstração, simulando a tabela original do problema:

-- Criação da base de testes
IF (OBJECT_ID('dbo.Dados') IS NOT NULL) DROP TABLE dbo.Dados
CREATE TABLE dbo.Dados (
    Nome VARCHAR(100),
    Cidade VARCHAR(100),
    Conteudo XML
)

INSERT INTO dbo.Dados
(
    Nome,
    Cidade,
    Conteudo
)
VALUES
('Dirceu Resende', 'Bridgetown','<Pessoa><Nome>Dirceu Resende</Nome><Conteudo>Teste</Conteudo></Pessoa>'),
('Lya', 'Vila Velha', '<Pessoa><Nome>Lya</Nome><Conteudo>Teste 1</Conteudo></Pessoa>'),
('Sula', 'Belo Hozionte', '<Pessoa><Nome>Sula</Nome><Conteudo>Teste 2</Conteudo></Pessoa>'),
('Letícia', 'Vitória', '<Pessoa><Nome>Lele</Nome><Conteudo>Teste 3</Conteudo></Pessoa>'),
('Rafael', 'Fortaleza', '<Pessoa><Nome>Rafa</Nome><Conteudo>Teste 4</Conteudo></Pessoa>'),
('Leandro', 'São Paulo', '<Pessoa><Nome>Mongo</Nome><Conteudo>Teste 5</Conteudo></Pessoa>'),
('Arthur', 'Brasília', '<Pessoa><Nome>iFan</Nome><Conteudo>Teste 6</Conteudo></Pessoa>'),
('Jhonathan', 'São Paulo', '<Pessoa><Nome>Jon</Nome><Conteudo>Teste 7</Conteudo></Pessoa>');

-- Criação da base de testes

IF (OBJECT_ID('dbo.Dados') IS NOT NULL) DROP TABLE dbo.Dados

CREATE TABLE dbo.Dados (

Nome VARCHAR(100),

Cidade VARCHAR(100),

Conteudo XML

)

INSERT INTO dbo.Dados

(

Nome,

Cidade,

Conteudo

)

VALUES

('Dirceu Resende', 'Bridgetown','<Pessoa><Nome>Dirceu Resende</Nome><Conteudo>Teste</Conteudo></Pessoa>'),

('Lya', 'Vila Velha', '<Pessoa><Nome>Lya</Nome><Conteudo>Teste 1</Conteudo></Pessoa>'),

('Sula', 'Belo Hozionte', '<Pessoa><Nome>Sula</Nome><Conteudo>Teste 2</Conteudo></Pessoa>'),

('Letícia', 'Vitória', '<Pessoa><Nome>Lele</Nome><Conteudo>Teste 3</Conteudo></Pessoa>'),

('Rafael', 'Fortaleza', '<Pessoa><Nome>Rafa</Nome><Conteudo>Teste 4</Conteudo></Pessoa>'),

('Leandro', 'São Paulo', '<Pessoa><Nome>Mongo</Nome><Conteudo>Teste 5</Conteudo></Pessoa>'),

('Arthur', 'Brasília', '<Pessoa><Nome>iFan</Nome><Conteudo>Teste 6</Conteudo></Pessoa>'),

('Jhonathan', 'São Paulo', '<Pessoa><Nome>Jon</Nome><Conteudo>Teste 7</Conteudo></Pessoa>');

Exemplo da tabela:

Como exportar o conteúdo de uma coluna para arquivos XML

Como parte da solução proposta para esse cenário, vou utilizar a Stored Procedure stpEscreve_Arquivo_FSO, disponível no artigo Operações com arquivos utilizando OLE Automation no SQL Server, para criar arquivos de texto com o conteúdo das colunas:

Clique para visualizar o código da Stored Procedure

CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] (
    @String VARCHAR(MAX),
    @Ds_Arquivo VARCHAR(1501)
)
AS
BEGIN

    
    --------------------------------------------------------------------------------
    -- Habilitando o OLE Automation (Se não estiver ativado)
    --------------------------------------------------------------------------------
 
    DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures')
 
    IF (@Fl_Ole_Automation_Ativado = 0)
    BEGIN
 
        EXECUTE sp_configure 'show advanced options', 1;
        RECONFIGURE WITH OVERRIDE;
    
        EXEC sp_configure 'Ole Automation Procedures', 1;
        RECONFIGURE WITH OVERRIDE;
    
    END


    DECLARE
        @objFileSystem INT,
        @objTextStream INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(1000),
        @Command VARCHAR(1000),
        @hr INT

    SET NOCOUNT ON

    SELECT
        @strErrorMessage = 'opening the File System Object'
    
    EXECUTE @hr = sp_OACreate
        'Scripting.FileSystemObject',
        @objFileSystem OUT

    
    IF @hr = 0
        SELECT
            @objErrorObject = @objFileSystem,
            @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"'
    
    
    IF @hr = 0
        EXECUTE @hr = sp_OAMethod
            @objFileSystem,
            'CreateTextFile',
            @objTextStream OUT,
            @Ds_Arquivo,
            2,
            True

    IF @hr = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"'
    
    
    IF @hr = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Write',
            NULL,
            @String

    
    IF @hr = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"'
    
    
    IF @hr = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Close'

    
    IF @hr <> 0
    BEGIN
    
        DECLARE
            @Source VARCHAR(255),
            @Description VARCHAR(255),
            @Helpfile VARCHAR(255),
            @HelpID INT
    
        EXECUTE sp_OAGetErrorInfo
            @objErrorObject,
            @Source OUTPUT,
            @Description OUTPUT,
            @Helpfile OUTPUT,
            @HelpID OUTPUT
        
        
        SELECT
            @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')
        
        
        RAISERROR (@strErrorMessage,16,1)
        
    END
    
    
    EXECUTE sp_OADestroy
        @objTextStream
    
    EXECUTE sp_OADestroy
        @objTextStream


    --------------------------------------------------------------------------------
    -- Desabilitando o OLE Automation (Se não estiver ativado)
    --------------------------------------------------------------------------------
 
    IF (@Fl_Ole_Automation_Ativado = 0)
    BEGIN
 
        EXECUTE sp_configure 'show advanced options', 0;
        RECONFIGURE WITH OVERRIDE;
    
        EXEC sp_configure 'Ole Automation Procedures', 0;
        RECONFIGURE WITH OVERRIDE;
    
    END
        
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] (

@String VARCHAR(MAX),

@Ds_Arquivo VARCHAR(1501)

)

BEGIN

--------------------------------------------------------------------------------

-- Habilitando o OLE Automation (Se não estiver ativado)

--------------------------------------------------------------------------------

DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures')

IF (@Fl_Ole_Automation_Ativado = 0)

BEGIN

EXECUTE sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Ole Automation Procedures', 1;

RECONFIGURE WITH OVERRIDE;

END

DECLARE

@objFileSystem INT,

@objTextStream INT,

@objErrorObject INT,

@strErrorMessage VARCHAR(1000),

@Command VARCHAR(1000),

@hr INT

SET NOCOUNT ON

SELECT

@strErrorMessage = 'opening the File System Object'

EXECUTE @hr = sp_OACreate

'Scripting.FileSystemObject',

@objFileSystem OUT

IF @hr = 0

SELECT

@objErrorObject = @objFileSystem,

@strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"'

IF @hr = 0

EXECUTE @hr = sp_OAMethod

@objFileSystem,

'CreateTextFile',

@objTextStream OUT,

@Ds_Arquivo,

True

IF @hr = 0

SELECT

@objErrorObject = @objTextStream,

@strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"'

IF @hr = 0

EXECUTE @hr = sp_OAMethod

@objTextStream,

'Write',

NULL,

@String

IF @hr = 0

SELECT

@objErrorObject = @objTextStream,

@strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"'

IF @hr = 0

EXECUTE @hr = sp_OAMethod

@objTextStream,

'Close'

IF @hr <> 0

BEGIN

DECLARE

@Source VARCHAR(255),

@Description VARCHAR(255),

@Helpfile VARCHAR(255),

@HelpID INT

EXECUTE sp_OAGetErrorInfo

@objErrorObject,

@Source OUTPUT,

@Description OUTPUT,

@Helpfile OUTPUT,

@HelpID OUTPUT

SELECT

@strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')

RAISERROR (@strErrorMessage,16,1)

END

EXECUTE sp_OADestroy

@objTextStream

EXECUTE sp_OADestroy

@objTextStream

--------------------------------------------------------------------------------

-- Desabilitando o OLE Automation (Se não estiver ativado)

--------------------------------------------------------------------------------

IF (@Fl_Ole_Automation_Ativado = 0)

BEGIN

EXECUTE sp_configure 'show advanced options', 0;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Ole Automation Procedures', 0;

RECONFIGURE WITH OVERRIDE;

END

Após criar essa Stored Procedure, crie o diretório de destino dos arquivos XML que serão gerados e execute o script abaixo:

-- Cria uma tabela temporária rankeada
IF (OBJECT_ID('tempdb..#Temporario_Ranking') IS NOT NULL) DROP TABLE #Temporario_Ranking
SELECT 
    *,
    ROW_NUMBER() OVER(ORDER BY Nome) AS Ranking
INTO
    #Temporario_Ranking
FROM
    dbo.Dados



-- Cria o looping para iterar em casa linha da tabela, recuperar as informações e gravar os arquivos
DECLARE
    @Contador INT = 1, -- Variável para iterar entre cada linha da linha
    @Total INT = (SELECT COUNT(*) FROM #Temporario_Ranking), -- Variável que vai guardar o total de linhas da tabela
    @Nome VARCHAR(100) -- Variável que vai guardar o valor da coluna "Nome" da linha atual da iteração
    @Conteudo VARCHAR(MAX) -- Variável que vai guardar o valor da coluna "Conteúdo" da linha atual da iteração,
    @Diretorio VARCHAR(500) = 'C:\Temporario\XML\', -- Diretório de destinos dos arquivos (deve ser criado previamente)
    @Nome_Arquivo VARCHAR(255) -- Nome do arquivo que será criado na iteração atual

WHILE (@Contador <= @Total)
BEGIN
    
    
    -- Recupera as informações a partir da linha atual
    SELECT
        @Nome = Nome,
        @Conteudo = CONVERT(VARCHAR(MAX), Conteudo),
        @Nome_Arquivo = @Diretorio + Nome + '.xml'
    FROM
        #Temporario_Ranking
    WHERE
        Ranking = @Contador


    -- Executa a Stored Procedure para criar o arquivo XML com o conteúdo recuperado na instrução anterior
    EXEC dbo.stpEscreve_Arquivo_FSO 
        @String = @Conteudo,    -- varchar(max)
        @Ds_Arquivo = @Nome_Arquivo -- varchar(1501)
    

    -- Incrementa o contador de iterações
    SET @Contador += 1

END

-- Cria uma tabela temporária rankeada

IF (OBJECT_ID('tempdb..#Temporario_Ranking') IS NOT NULL) DROP TABLE #Temporario_Ranking

SELECT

ROW_NUMBER() OVER(ORDER BY Nome) AS Ranking

INTO

#Temporario_Ranking

FROM

dbo.Dados

-- Cria o looping para iterar em casa linha da tabela, recuperar as informações e gravar os arquivos

DECLARE

@Contador INT = 1, -- Variável para iterar entre cada linha da linha

@Total INT = (SELECT COUNT(*) FROM #Temporario_Ranking), -- Variável que vai guardar o total de linhas da tabela

@Nome VARCHAR(100) -- Variável que vai guardar o valor da coluna "Nome" da linha atual da iteração

@Conteudo VARCHAR(MAX) -- Variável que vai guardar o valor da coluna "Conteúdo" da linha atual da iteração,

@Diretorio VARCHAR(500) = 'C:\Temporario\XML\', -- Diretório de destinos dos arquivos (deve ser criado previamente)

@Nome_Arquivo VARCHAR(255) -- Nome do arquivo que será criado na iteração atual

WHILE (@Contador <= @Total)

BEGIN

-- Recupera as informações a partir da linha atual

SELECT

@Nome = Nome,

@Conteudo = CONVERT(VARCHAR(MAX), Conteudo),

@Nome_Arquivo = @Diretorio + Nome + '.xml'

FROM

#Temporario_Ranking

WHERE

Ranking = @Contador

-- Executa a Stored Procedure para criar o arquivo XML com o conteúdo recuperado na instrução anterior

EXEC dbo.stpEscreve_Arquivo_FSO

@String = @Conteudo, -- varchar(max)

@Ds_Arquivo = @Nome_Arquivo -- varchar(1501)

-- Incrementa o contador de iterações

SET @Contador += 1

END

Após a execução do script abaixo, realizando os ajustes necessários para o seu cenário, o diretório de destino deverá ter sido populado com conteúdo da coluna do tipo XML, sendo um arquivo para cada linha da tabela.

Espero que tenham gostado dessa dica rápida e bem útil no dia a dia, especialmente de quem está começando.

Um grande abraço e até a próxima!

Cristiano disse:

22 de abril de 2024 às 14:23

Prezados, boa tarde!
A dúvida do Gilson Msg 50000, Level 16, State 1, Procedure stpEscreve_Arquivo_FSO, Line 107… Alguém tem alguma saída contorno?

Responder
Erika Macedo disse:

20 de novembro de 2023 às 15:08

Muito obrigada Dirceu Resende, Vc é verdadeiro contribuinte de conhecimentos.
Me ajudou muito SQL Server – Como exportar o conteúdo de uma coluna para arquivos XML.

Responder
Gilson A. Rodrigues disse:

24 de maio de 2022 às 09:18

Que ótimo trabalho, obrigado, estou tendando adaptar o script para uma necessidade aqui na empresa, mas ele só exporta os primeiros 256 arquivos xml, o último é gravado vazio, depois disso a query continua mas nenhum arquivo mais é gravado. Uma mensagem de erro é mostrada: Msg 50000, Level 16, State 1, Procedure stpEscreve_Arquivo_FSO, Line 107
Error whilst opening the File System Object,. Já tentei outras pastas, verifiquei permissões, etc, não consegui sair disso, pode me dar uma dica?

Responder
Thomas disse:

1 de julho de 2021 às 11:50

como faria para a saída do arquivo estar no formato UTF8 SEM BOM?

Responder
JORGE TAVARES JUNIOR disse:

26 de maio de 2020 às 12:51

muito massa parabéns

Responder

Assinar blog por e-mail

Visualizações do Blog

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Como exportar o conteúdo de uma coluna para arquivos XML

Como exportar o conteúdo de uma coluna para arquivos XML

You may also like...

5 Responses

Deixe uma respostaCancelar resposta

Como exportar o conteúdo de uma coluna para arquivos XML

You may also like...

6º Encontro do Chapter SQL Server ES (PASS Local Group do Espírito Santo) – 24/02/2018

SQL Server – Como exportar e importar arquivos com dados tabulares (Ex: CSV) utilizando o CLR (C#)

SQL Server 2016 – Como arquivar tabelas históricas no Azure com o Stretch Database

5 Responses

Deixe uma respostaCancelar resposta