Export XML to Formatted File

• beath

  SSCrazy

  Points: 2939

  More actions

  April 5, 2005 at 7:12 am

  #88039

  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

• Bert De Haes

  Hall of Fame

  Points: 3126

  More actions

  April 6, 2005 at 1:11 am

  #550701

  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

• beath

  SSCrazy

  Points: 2939

  More actions

  April 6, 2005 at 4:35 am

  #550730

  Ah - thanks Bert, it does just what I need.

  cheers

  Gordon