SQL Server - How to export the contents of a column to XML files

Views: 2.555 views

Reading Time: 4 minutes

Hey guys!!
On Thursday (09/04), I saw a very cool question in the Telegram group SQL Server - DBA, the largest group of DBA's and SQL Server Developers in the world, where the person had a table in the bank, where a column was of the XML type, and he would like to export to disk, each row of that table as a separate XML file and I found that would give a cool article to demonstrate that solution.

To export the table data to text files on the disk, I will use the solutions shared in the article SQL Server - How to export database data to text file (CLR, OLE, BCP).

If you are interested in learning how to better manipulate and treat XML files within SQL Server, I suggest reading the article SQL Server - How to read, import, and export data from XML files.

For this example, I will use the script below to create a demo table, simulating the original problem table:

-- 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>');

Table example:

How to export the contents of a column to XML files

As part of the proposed solution for this scenario, I will use the Stored Procedure stpWrite_File_File, available in the article File Operations Using OLE Automation in SQL Server, to create text files with the contents of the columns:

Click to view the Stored Procedure code

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

After creating this Stored Procedure, create the destination directory of the XML files that will be generated and run the script below:

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

After running the script below, making the necessary adjustments for your scenario, the destination directory must have been populated with XML column content, with one file for each row in the table.

I hope you enjoyed this quick and very useful tip on a daily basis, especially for those just starting out.

A big hug and until next time!

Gilson A. Rodrigues said:

24 May 2022 to 09: 18

What a great job, thanks, I'm trying to adapt the script to a need here in the company, but it only exports the first 256 xml files, the last one is written empty, after that the query continues but no more files are written. An error message is displayed: Msg 50000, Level 16, State 1, Procedure stpEscreve_Arquivo_FSO, Line 107
Error whilst opening the File System Object,. I've already tried other folders, checked permissions, etc, I couldn't get out of this, can you give me a hint?

Thomas said:

1 July 2021 to 11: 50

how would you make the output of the file to be in UTF8 format WITHOUT GOOD?

JORGE TAVARES JUNIOR said:

26 May 2020 to 12: 51

lots of congratulations

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to export the contents of a column to XML files

How to export the contents of a column to XML files

You may also like ...

Comments

Leave a Comment Cancel reply

How to export the contents of a column to XML files

You may also like ...

SQL Server and Power BI: How to load Stored Procedure data into SQL Server with DirectQuery

SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)

Is SQL Server Configuration Manager gone? Learn how to recover the shortcut

Comments

Leave a Comment Cancel reply