generate xml using sql query

• rams.prsk

  Ten Centuries

  Points: 1241

  More actions

  April 10, 2012 at 6:46 am

  #253765

  Hi All,

  I want to generate an xml from the below tables using sql query(sql 2008) in the following format :

  <SubmitMessage version="B000">

  <Authentication id="57985775">AYUPXNHD</Authentication>

  <ForwardMessage category="data">

  <AdC>4799982</AdC><AlertCode>3</AlertCode>

  <MessageData infotype="15">0A06420006C02002010100C3</MessageData>

  <Flags ack="1" />

  </ForwardMessage>

  </SubmitMessage>

  Here are the three tables and its data in detailed format

  table1:

  ControlStationID Password

  57985775 AYUPXNHD

  table2:

  ID TerminalID Type

  14799982 1

  table3:

  ID MessageData

  10A06420006C02002010100C3

  20B06420007C02002020300C3

  kindly help me on the same

  Thanks

• Jared

  SSC Guru

  Points: 61793

  More actions

  April 10, 2012 at 8:33 am

  #1471240

  Please refer to the article linked in my signature by Jeff Moden to see how to properly post your question.

  Jared
  CE - Microsoft

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  April 10, 2012 at 8:46 am

  #1471247

  Try this one too. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/