April 5, 2005 at 7:12 am
Hi
I'm trying to write an XML query that will be exported to a file. I'm using the sp_makewebtask stored procedure as shown:
EXEC sp_makewebtask
@outputfile = 'd:\sqldata\Output.xml',
@query = 'SELECT * FROM etc. etc. FOR XML EXPLICIT'
Because this returns the xml on a single line it only works for small datasets. Once I start to return a larger amount of data the xml gets wrapped onto multiple lines and I get the error "Whitespace is not allowed at this location"
How can I get SQL to output to a properly formatted XML file? Can it be done through normal TSQL or does it involve more complicated programming?
Thanks
April 6, 2005 at 1:11 am
You can try the bcp utility to get the data out of SQL server.
Try this : ( the generated xml is not OK, as the AUTO statement does not generate correct xml, but it is just an exemple ... )
declare @sql varchar(1024)
declare @outfile varchar(255)
declare @cmd varchar(8000)
set @sql = 'select * from pubs.dbo.sales FOR XML AUTO'
set @outfile = 'd:\temp\sales.xml'
set @cmd = 'bcp '
+ '"' + @sql + '"'
+ ' queryout '
+ '"' + @outfile + '"'
+ ' -S ' + @@servername + ' -c -t "" -r "" -T'
exec master.dbo.xp_cmdshell @cmd, no_output
April 6, 2005 at 4:35 am
Ah - thanks Bert, it does just what I need.
cheers
Gordon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply