Format xml output

• d perry

  SSC Journeyman

  Points: 83

  More actions

  October 27, 2010 at 9:04 am

  #225570

  Been looking for a solution to this problem for a couple of days and have not found anything to format my output correctly.

  Using query

  SELECT amazon_id as MessageID,

  'Update' as OperationType,

  sku as "SKU",

  quantity as "Quantity"

  FROM tblapparel

  WHERE (update_type = 2)

  Format like

  <Message>

  <MessageID>1</MessageID>

  <OperationType>Update</OperationType>

  <Inventory>

  <SKU>123456</SKU>

  <Quantity>8</Quantity>

  </Inventory>

  </Message>

  Thanks for any help

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  October 27, 2010 at 9:59 am

  #1243012

  If you work with SQL Server 2000, my answer won’t work for you. Next time pleas give us more information. I suggest that you’ll fallow the link in my signature to an article that shows how to ask questions in a better way (this will also help you getting answer faster and the answers will be better). As for your question – you can use the FOR XML clause at the end of the query. The code bellow shows how to do it:

  select amazon_id as MessageID,

  'Update' as OperationType,

  sku as 'Inventory/SKU',

  quantity as 'Inventory/Quantity'

  from tblapparel

  for xml path('Message')

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/