SQL Server 2005 FOR XML

Question

I want to export data from a table to a specifically formatted XML file. I am fairly new to XML files, so what I am after may be quite obvious but I just cant find what I am looking for on the net.

The format of the XML results I need are:

    <data>
        <event 
            start="May 28 2006 09:00:00 GMT"
            end="Jun 15 2006 09:00:00 GMT"
            isDuration="true"
            title="Writing Timeline documentation"
            image="http://simile.mit.edu/images/csail-logo.gif">
            A few days to write some documentation
        </event>
     </data>

My table structure is:

name VARCHAR(50),
description VARCHAR(255),
startDate DATETIME,
endDate DATETIME

(I am not too interested in the XML fields image or isDuration at this point in time).

I have tried:

SELECT [name]
           ,[description]
           ,[startDate]
           ,[endTime]

  FROM [testing].[dbo].[time_timeline]
  FOR XML RAW('event'), ROOT('data'), type

Which gives me:

<data>
    <event name="Test1" 
           description="Test 1 Description...." 
           startDate="1900-01-01T00:00:00" 
           endTime="1900-01-01T00:00:00" 
    />
    <event name="Test2" 
           description="Test 2 Description...." 
           startDate="1900-01-01T00:00:00" 
           endTime="1900-01-01T00:00:00" 
    />
</data>

What I am missing, is the description needs to be outside of the event attributes, and there needs to be a tag.

Is anyone able to point me in the correct direction, or point me to a tutorial or similar on how to accomplish this?

Thanks,

Matt

Answer 1

This should do the job:

SELECT
    name "event/@name"
    , startDate "event/@start"
    , description "event"
FROM
    [testing].[dbo].[time_timeline]
FOR XML PATH(''), ROOT('data')

Things to note:

• In order to get description as the text content of event, we have to 'step up' a level and use PATH(''), and specify the name event in the alias for all columns
• All attribute-centric columns must come before all non-attribute-centric columns

For learning this stuff (or at least getting an idea of how to do what you want), see the docs for FOR XML and just play around with your own tables and desired XML structures.

Answer 2

Consider switching to FOR XML PATH (see http://msdn.microsoft.com/en-us/library/ms189885.aspx for documentation)

A very partial example:

SELECT
    [name] 
   ,[description] 
   ,[startDate]   "@start"
   ,[endTime]     "@end"
  FROM [testing].[dbo].[time_timeline] 
  FOR XML PATH('event'), ROOT('data')