FOR XML AUTO,ELEMENTS (Not working Properly)

• Mobashir Ahmed

  SSC Veteran

  Points: 209

  More actions

  August 17, 2005 at 10:58 am

  #67469

  I am using "FOR XML AUTO,ELEMENTS" to export data as an XML file. I don't know if there's any limitation on "FOR XML AUTO", but as soon as my result set gets bigger "FOR XML AUTO" dosen't seems to work properly, it do exports the data as XML file but with messed up format means it puts the carrage return right in the middle of an element.  e.g

  </User

  Ref1>

  where </UserRef1> is a well formed XML. Can any please help with this...am I doing something wrong or there is a limitation on number of row with "FOR XML AUTO".

  Below is the query I am using.

  Thanks in Advance

   

  EXEC sp_makewebtask

  @outputfile = 'C:\EDrive\MedNet\Shippers.xml',@query = 'select MembershipNo,PackageSKU,UserRef1,UserRef2,Type,OrderDate,FirstName,LastName,Address1,

  City,State,ZipCode

  from AccessoryOrder 

  inner  join Shipping ON AccessoryOrder.everybody_id = Shipping.everybody_id

  FOR XML AUTO,ELEMENTS',

  @templatefile ='C:\EDrive\MedNet\Shippers.txt'

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  August 17, 2005 at 11:11 am

  #582837

  That is something you'll have to deal with when you get a large string back from sql.

  SqlServer outputs data in 8k chunks. So a new chunk is starting after the carriage return.

  Check this thread out,

  http://www.sqlxml.org/faqs.aspx?faq=29