MSSQL 2005 FOR XML
- by Lima
Hi,
I am wanting to export data from a table to a specifically formated 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
