At one stage you say that auto mode does not allow you to specify a root element, and you provide a work around. Are you perhaps talking about SQL 2000? As by using the following construct with SQL 2005, you can very simply provide a root element.
FOR XML AUTO, ROOT ('CustomersByRegion')
You are right. Thakx for pointing this.
This is very useful, Jacob, and nicely explained. Thanks for that. I loved the format. I find PATH the most intuitive way of generating XML to a precise format. I really struggled before I started using it.
p.s. microsoft.public.sqlserver.xml can be really useful for people like me who are still slightly mystified by aspects of XML.
Thank you for the comments. I am a regular reader of your articles at simple-talk. They are simply excellent :-).
There is an article on EXPLICIT already on the way. It may be appearing in the next few weeks.