Make MS SQL 2008 generate XML like MySQL 5.0?

Question

A while ago I wrote an app that relied on XML coming out of a MySQL 5.0 database. It got this XML output by calling the mysql client directly, using the --xml command-line option; this made MySQL output XML that looked kind of like this:

<resultset statement="SELECT * FROM tablename " 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
    <row>
        <field name="some_int">2</field>
        <field name="some_varchar">a string</field>
    </row>
</resultset>

Now I'm trying to switch over to MS SQL 2008. I have the tables ported OK, and the client side can call pretty much whatever it wants to, so no problem there. My issue is in getting MS SQL to output the expected XML - is there an easy way to produce this same structure?

So far I've looked at the FOR XML option in a SELECT statement, and it seems really close to what I want to do (using the PATH variant), but there's one minor tweak: it doesn't output <field> tags anymore, but instead names the tags with the column name, so the above XML becomes:

<row>
    <some_int>2</some_int>
    <some_varchar>a string</some_varchar>
</row>

(Note that the <resultset> tag is also gone; this is not a huge issue, but if MS SQL can generate that too, bonus points.)

Any easy way to get the same XML structure from MS SQL as MySQL produced, short of writing my own XML generator? Ideally, I'd like to use any combination of FOR XML in the SELECT, sqlcmd, or osql commands to generate the right text - doesn't have to be pretty.

Edit: The statement I'm running is a very straightforward SELECT * FROM tablename.

Answer 1

I think your best best would be to use XSD. To get it into the same format you would have to do some manipulation on your own but you could get the data out as XML with very little hassle.

http://msdn.microsoft.com/en-us/library/aa258637(SQL.80).aspx