SQL - Crear un archivo .xml a desde un SP SQL

--Declare variables

DECLARE @fileText VARCHAR(MAX);

DECLARE @fileName VARCHAR(500) ;

DECLARE @command varchar(1000);

--

--Declare parameters

--

--Here you need to specify the output directory for the files to be created

DECLARE @outputDir VARCHAR(250)='c:\tmp\files\';

--Here you need to specify the SQL instance from which the data will be exported

DECLARE @instanceName VARCHAR(50)='.\SQL2K14'

--Here you specify whether you are connecting to the SQL instance with a

--trusted connection (Windows Authentication) or not

DECLARE @isTrustedConnection BIT=1

--If isTrustedConnection is set to 0 then you will need to

--add username and password for connecting to the SQL Server instance

DECLARE @userName VARCHAR(50)='loginName'

DECLARE @password VARCHAR(50)='password'

--Create temporary table if it does not exist

IF OBJECT_ID('TestDB..tmpSource') IS NULL

CREATE TABLE TestDB..tmpSource(

 [fileText] [TEXT] NULL

);

--Declare cursor and populate with data

DECLARE db_cursor CURSOR FOR

SELECT [fileText], [fileName]

FROM TestDB..FileContents

WHERE [code] IN ('code2', 'code3');

--Open cursor

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @fileText, @fileName

WHILE @@FETCH_STATUS = 0

BEGIN

    --Clean temporary table

    DELETE FROM TestDB..tmpSource

    --Insert record into temporary table

    INSERT INTO TestDB..tmpSource (fileText) VALUES(@fileText);

    --Dynamically construct the BCP command

    --If the user connects to the SQL instance using a trusted connection

    IF (@isTrustedConnection=1)

       SET @command = 'bcp "SELECT * FROM TestDB..tmpSource" queryout "'+ @outputDir + @fileName + '" -S ' + @instanceName +' -T -c'

    --If the user connects to the SQL instance using username/password

    ELSE

       SET @command = 'bcp "SELECT * FROM TestDB..tmpSource" queryout "'+ @outputDir + @fileName + '" -S ' + @instanceName +' -c -U ' + @userName + ' -P '+ @password

    --Execute the BCP command

    EXEC xp_cmdshell @command, no_output

    --Fetch next records

    FETCH NEXT FROM db_cursor INTO @fileText, @fileName

END

--Close and deallocate cursor

CLOSE db_cursor

DEALLOCATE db_cursor

GO

DECLARE @noDoctoFiscal VARCHAR(100)

DECLARE @Ext VARCHAR(10)

DECLARE @Command NVARCHAR(4000)

DECLARE cAttachments CURSOR FOR

SELECT  noDoctoFiscal,XMLDoc

FROM TECAPP.dbo.xDoctoFiscalDigital where folioDigital >=66847

ORDER BY noDoctoFiscal

OPEN cAttachments

FETCH NEXT FROM cAttachments

INTO @noDoctoFiscal, @Ext

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @Command = 'bcp "SELECT XMLDoc FROM TECAPP.dbo.xDoctoFiscalDigital

                WHERE noDoctoFiscal = ''' + @noDoctoFiscal + '''"

                queryout "C:\' + @noDoctoFiscal + '.xml'  + '" -c -T'

    PRINT @Command

    FETCH NEXT FROM cAttachments

    INTO @noDoctoFiscal, @Ext

END

CLOSE cAttachments

DEALLOCATE cAttachments

Exec Master..xp_Cmdshell 'bcp "SELECT XMLDoc FROM TECAPP.dbo.xDoctoFiscalDigital

    WHERE noDoctoFiscal = 'TEC66847'"

    queryout "C:\TEC66847.xml" -c -T

SET @Command ='Exec Master..xp_Cmdshell ''bcp "SELECT XMLDoc FROM TECAPP.dbo.xDoctoFiscalDigital

    WHERE noDoctoFiscal = ''' + @noDoctoFiscal + '''"

    queryout "C:\' + @noDoctoFiscal + '.xml'  + '" -c -T'