¡¡Hola, chicos!!
El jueves (04/09), vi una pregunta realmente interesante en el grupo de Telegram. Servidor SQL – DBA, el grupo más grande de desarrolladores de DBA y SQL Server en el mundo, donde la persona tenía una tabla en el banco, donde una columna era de tipo XML, y le gustaría exportar al disco, cada fila de esa tabla como un archivo XML separado y pensé que sería un artículo interesante para demostrar esta solución.

Para exportar datos de tablas a archivos de texto en el disco, usaré las soluciones compartidas en el artículo. SQL Server: cómo exportar datos de bases de datos a archivos de texto (CLR, OLE, BCP).

Si está interesado en aprender mejor cómo manipular y tratar archivos XML dentro de SQL Server, le sugiero leer el artículo. SQL Server: cómo leer, importar y exportar datos desde archivos XML.

Para este ejemplo, usaré el siguiente script para crear una tabla de demostración, simulando la tabla original del 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>');

Ejemplo de tabla:

Cómo exportar el contenido de una columna a archivos XML

Como parte de la solución propuesta para este escenario, usaré el procedimiento almacenado stpWrite_File_FSO, disponible en el artículo Operaciones de archivos usando OLE Automation en SQL Server, para crear archivos de texto con el contenido de las columnas:

Haga clic para ver el código del procedimiento almacenado
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

Después de crear este procedimiento almacenado, crear directorio de destino para archivos XML que se generará y ejecutará el siguiente script:

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

Después de ejecutar el siguiente script, hacer los ajustes necesarios para su escenario, el directorio de destino debe haberse llenado con contenido de columna de tipo XML, con un archivo para cada fila de la tabla.

Espero que hayas disfrutado de este consejo rápido y muy útil en la vida cotidiana, especialmente para aquellos que recién comienzan.

¡Un abrazo grande y hasta la próxima!