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

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

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

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!