Lots of RIA's use web services, and one of the more tedious parts of writing a service can be looping through all of the rows in a recordset, adding nodes, setting their attributes, etc. I spend a lot of time trawling through MSDN trying to speed up my development process, and fairly early I found an intriguing syntax (SELECT ... FOR XML) that seemed to hold out the promise of allowing SQL Server to do most of the XML coding for me. But what I found is that all the examples took the result of the XML and put it in a variable of XML type. It took me a while to figure out how to take that XML and make it available outside the procedure, since if you use normal open recordset syntax, what you wind up with in your one recordset row/column won't populate a DomDocument object properly on the HTML side. All the examples that I could find "stop at the door" as it were, and don't really explain how you take that XML typed "thing" and make it into something that you can load into your DomDocument.
The method I hit on myself is to put the XML object into a variable, cast that to varChar and select that. Then on the page, you can load the string in as XML using the loadXML method of your DomDocument object.
Later, I found this article on SitePoint which shows a different method, loading a stream instead of a recordset. Arguably it is a better method, though I think mine fits my development style better (I can print the string to the Messages window while developing and feel happy that my XML looks like I want it to). I also prefer the Path Mode to Explicit Mode, as I find it more intuitive and less complicated.
Happy coding!