Apparently, since I last touched SQL Server they’ve added some pretty awesome XML support.
SpecId as "@Id",
Name as "@Name",
Description as "Description"
FOR XML PATH('Spec'), TYPE;
Returns XML straight out of SQL Server. The best thing? The “TYPE” clause on the FOR XML part makes it return as an XML data type (which is also something new), meaning you can do some cool things like write functions that return XML that you can use to build up collections, which can be included in your XML (I use it for collections of children, pretty much solving the hierarchical data problem I always used to have).
I know this is really ancient news for anyone who works with SQL every day, but it’s been a few years for me, and I thought it was pretty awesome.