creating N xml documents from N rows

kudos to anyone who can answer this one;

I've got a table and I want to select rows in it so that the result contains an XML column. So going from

ID Parent Name Date

1 null Foo 2005-04-03

2 1 Bar 2005-04-03

to

ID Parent XML

1 null <element name="Foo" date="2005-04-03" />

2 1 <element name="Bar" date="2005-04-03" />

And for the life of me I can't find a method for creating the XML in an elegant way.

The 'FOR XML EXPLICIT' clause gives me all the formatting I could want, but forces you to munge all the XML into a single, xml-only result. That doesn't seem to be a help. Is there a clean way to do this?

If all else fails, though I shudder even to suggest it, is there a function to xml-escape strings so I can do something like

select

ID, Parent,

convert(xml, '<element name="' + quote(name) + '" date="' + quote(tostring(date)) + '" />")

from Elements

If you SQL Server 2005 you can use the following query,

Code Snippet

Create Table #data (

[ID] int ,

[Parent] Varchar(100) ,

[Name] Varchar(100) ,

[Date] datetime

);

Insert Into #data Values('1',NULL,'Foo','2005-04-03');

Insert Into #data Values('2','1','Bar','2005-04-03');

Select

Id

,Parent

,(Select [Name],[Date] from #Data element Where element.Id=Main.ID For XML AUTO) as XML

From

#Data Main

|||Wow -- thanks. That's absolutely perfect. Thanks so much.|||

this one also will work:

select
ID,
Parent,
(
select
quote(name) as "@.name",
quote(tostring(date)) as "@.daae"
for xml path('element'), type
) xml
from Elements