XML to file using BCP

  • Hi,

    I am having trouble with exporting data into an XML format using the BCP method. The XML file is perfectly fine when taken directly out of the results window, however there seems to be something fundamentally wrong with the XML when written to file.

    This is the method I am using, which calls the procedure further down.

    -Method

    DECLARE @FileLocation VARCHAR(255) = 'S:\temp'

    DECLARE @OutputFile VARCHAR(255) = @FileLocation + '\File001_' + CONVERT(CHAR(8),@StartDate,112) + '_' + CONVERT(CHAR(8),@EndDate,112) + '.xml'

    DECLARE @EmailSubject VARCHAR(255) = 'Latest file'

    DECLARE @EmailBody VARCHAR(255) = 'Please find the latest file attached for date range from:' + CONVERT(CHAR(8),@StartDate,112) + ' to: ' + CONVERT(CHAR(8),@EndDate,112)

    EXECUTE rpt.spSaveXMLVariableToFile @XMLBody, @OutputFile

    -Procedure

    ALTER PROCEDURE [rpt].[spSaveXMLVariableToFile]

    @TheXML XML,

    @Filename VARCHAR(255)

    AS

    SET NOCOUNT ON

    DECLARE @MySpecialTempTable VARCHAR(255)

    DECLARE @Command NVARCHAR(4000)

    DECLARE @RESULT INT

    SELECT @MySpecialTempTable = '##temp' + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))

    SELECT @Command = 'create table [' + @MySpecialTempTable + '] (MyID int identity(1,1), Bulkcol XML) insert into [' + @MySpecialTempTable + '](BulkCol) select @TheXML'

    EXECUTE sp_ExecuteSQL @command, N'@TheXML XML', @TheXML

    SELECT @Command = 'bcp "select BulkCol from [' + @MySpecialTempTable + ']' + '" queryout ' + @Filename + ' ' + '-w' + ' -T -S' + @@servername

    EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT

    EXECUTE ( 'Drop table ' + @MySpecialTempTable )

    RETURN @result

    The output file is used for submitting to an online reporting platform when it is created using the above method it doesn't work. Unfortunately I don't receive any errors back where I have received them with other issues when testing, in this case the file just disappears as tho it hasn't been submitted.

    Anyone have any ideas/ suggestions?

    Regards
    ld

    Stoke-on-Trent
    United Kingdom

    If at first you don't succeed, go to the pub and drink away your current thought plan.

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply