Root node from a FOR XML

Hello,
How can I get a XML with a root node from a FOR XML ?:
Now I have this:
The SQL:

SELECT * FROM Table_Projects Project
FOR XML AUTO

and the XML output:

<Project>
<ItemID>1</ItemID>
....
</Project>
<Project>
<ItemID>2</ItemID>
....
</Project>
...

and I want this:

<Projects>
<Project>
<ItemID>1</ItemID>
....
</Project>
<Project>
<ItemID>2</ItemID>
....
</Project>
...
</Projects>

thanks and regards,
Edu
What client are you using, just TSQL or ADO or ADO.net, etc|||Hello pkr,
I'm using ADO.NET on an ASP.NET page (with c#)
Now I am using the following code to solve the problem:

XmlDocument xmlDoc =new XmlDocument();

myConnection.Open();

SqlDataReader dr = myCommand.ExecuteReader();

string stringXml = "<Projects>";

while (dr.Read())

{

stringXml += dr.GetString(0);

}

stringXml += "</Projects>";

xmlDoc.LoadXml(stringXml);

myConnection.Close();

return(xmlDoc);

But I am searching for a solution on the SQL Server side ( without SQLXML 3.0 for the moment)
Regards,
Edu
|||Ah yes the holy grail of avoiding SQLXML, sorry I don't believe there is such a beast, at least not yet. One, and I think horrible, way it to use mutliple results sets...
<code>
Select '<root>'
Select * from table for xml raw
Select '</root>'
</code>
Horrible isn't? I think the way you're currently doing is the best of a bad bunch.|||I've done this with the use of FOR XML EXPLICIT and a UNION ALL. Here are 3 resources on this topic that I've personally found to behelpful:

SQL Server 2000, XML and XSL: The Ultimate Combination
Retrieving XML from SQL Server Using SELECT ... FOR XML
Demystify SQL Server FOR XML EXPLICIT

|||Yes good point, although IMHO xml explicit is a horrible syntax, luckily they've done a much better job in 2005.