Creating XML from SQL Server Data that Conforms to a Specific Schema

Question

I've got data in a SQL Server 2005 table that I need to insert into an application using a SOAP web service that takes in an XML fragment. I'm looking at different options for this and I would like to know if it is possible to get SQL Server to return me XML that conforms to a particular Schema. If it is possible, how do I do it?

Answer 1

Been a while since I did this, but an explicit option on FOR XML exists, and the auto can usually be bent to task with appropriate aliasing, but for maximum abstraction I believe that when I last did this we used an XSLT transform to shape the SQL output XML to the desired output schema, that's the approach I'd take again subject to benchmarking. XSLT is very much designed for exactly this kind of task, and is considerably easier to change that a production database.

Part of the USP of 2k5 was it's improved XML handling, you should find a number of options available for any given task.