Help with XML Explicit and nesting

• mandy-1024472

  Grasshopper

  Points: 17

  More actions

  March 18, 2013 at 10:30 am

  #291240

  Hello,

  I currently have a very basic SQL statement to dump my results out in XML format:

  SELECT

  1 AS tag,

  NULL AS parent,

  BookingID as [Event!1!BookingID!cdata],

  WebTitle as [Event!1!EventName!cdata],

  Room AS [Event!1!Room!Element],

  Audience as [Event!1!Audience!cdata]

  FROM MyEvents

  ORDER BY TimeBookingStart

  FOR XML EXPLICIT

  The resulting XML looks like this:

  <Event>

  <BookingID>

  <![CDATA[14957]]>

  </BookingID>

  <WebTitle>

  <![CDATA[This is the title]]>

  </WebTitle>

  <Room>

  Room 1255

  </Room>

  <Audience>

  <![CDATA[Private]]>

  </Audience>

  </Event>

  Now I would like to add a new element for EventType. There can be more than one EventType specified per <Event>. Can someone show me how to add an additional element that may include several distinct values? For example, booking Id 14957 has two records in the database each with a different eventtype value.

  Thank you in advance.

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 18, 2013 at 11:37 am

  #1598124

  Welcome to the forum!

  Unfortunately , details provided with your question are not sufficient enough to make any helpful suggestions - only guesses, which quite often will be irrelevant. Therefore, I strongly advise you to read the article from the link at the bottom of my signature which will explain the best way to ask questions on this forum (eg. providing DDL, sample data and clear expected output).

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]