May 9, 2016 at 3:59 am
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