formatted xml with 'for xml'

• zeeshan asghar

  Newbie

  Points: 4

  More actions

  July 7, 2011 at 3:52 am

  #243931

  Dear All,

  my query is below which is working fine

  WITH tempPagedag AS

  (

  SELECT *, ROW_NUMBER() OVER

  (order by agency_ID) as RowNumber FROM Agency

  )

  SELECT * FROM tempPagedag WHERE RowNumber > 0 AND RowNumber <= 50 for xml auto

  but 'for xml' clause generates attribute based xml and i want element based xml.

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  July 7, 2011 at 6:55 am

  #1349650

  Use FOR XML PATH instead of FOR XML AUTO.

  Another options is to use the ELEMENTS directive.

  FOR XML AUTO, ELEMENTS

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA