We had a requirement to generate some extract data in the form of XML.
So while designing the solution, I had 2 options.
1. Use FOR XML in the Query with SQL 2005
2. Use regular SQL Queries and usign a Custom DataSet, serialize the data to XML.
SQL 2005 has nice options to get the XML.
You can assign name spaces to nodes, can have nested XMLs, can have custom Root and Child Xml node names.
One example can be found here.
http://www.wrox.com/WileyCDA/Section/id-301088.html
But FOR XML queries are much slower when getting huge amounts of data.
You can find a comparison here.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1265579,00.html
So the bottom line is, FORXML is suitable only for small output.
Else do the logic in the business components.
And in most of the circumstances, the load on the database need to be kept low.
Hence second method was found the more suitable one.