Exporting XML in SQL Server Stored Procedure

Question

Hi everyone,

I've written a stored procedure on SQL Server to return an xml that then is shown on a Web Service, and is caught by a LineChart in Flex to show its values.

The problem is I need to show different values for each period(I have three different periods: Current, 6 month, 1 year) and it's getting hard for me to generate the xml for the three of them at SQL Server.

Right now I've done it for the Current period, and I don't know how to "concatenate" the other two periods. This xml looks like this:

<dataset>
  <item>
    <topic>MyBlog</topic>
    <topicid>XXXXXXXXXX</topicid>
    <popularity>1</popularity>
    <period>11/01/2009</period>
  </item>
</dataset>

And I'd like it to be something like this:

<dataset>
  <item>
    <topic>MyBlog</topic>
    <topicid>1111</topicid>
    <popularity>6</popularity>
    <period>Current</period>
  </item>
  <item>
    <topic>MyBlog</topic>
    <topicid>1111</topicid>
    <popularity>4</popularity>
    <period>6 month</period>
  </item>
  <item>
    <topic>MyBlog</topic>
    <topicid>1111</topicid>
    <popularity>1</popularity>
    <period>1 year</period>
  </item>
</dataset>

So, I don't know if I'm being clear enough, but although the XML looks weird, I need it this way so it's compatible with the chart's logic.

If you need further information just let me know.

Thanks,

Brian

Answer 1

Select *
From
(
    Select '1' As Col1, 'A' As Col2
    Union
    Select '2' As Col1, 'B' As Col2
) Item
For Xml Auto, Root('Alphabet'), Elements