Nested FOR XML results with SQL Server’s PATH mode

Query output as XML from SQL Server...

• Nicely formatted and properly mapped XML (e.g. no <row> elements as found in FOR XML RAW mode)
• To be able to easily map columns to XML elements and attributes
• A single root node, so I can load it into an XmlDocument without having to create the root node myself
• Nested child elements
• Not to have to turn my elegant little query into a huge mess of esoteric T-SQL (as with [Explicit!1!Mode])

http://richarddingwall.name/2008/08/26/nested-xml-results-from-sql-server-with-for-xml-path/