SQL to generate XML of table data

I'm trying to generate XML that will output the structure and data of any table using SQL 2008.

So, for example, you would pass in a table name to this stored procedure and it would generate XML in this format:

<TABLE name="mytable">
  <ROW>
    <COL name="firstname">John</COL>
    <COL name="lastname">Smith</COL>
  </ROW>
  <ROW>
    <CO开发者_JS百科L name="firstname">Bob</COL>
    <COL name="lastname">Jones</COL>
  </ROW>
</TABLE>

If there was a table called mytable (firstname varchar(100), lastname varchar(100))

You would be able to call stored procedure like this:

exec spGenerateTableData @tablename='mytable'

The trick I can't get is how to make the column names attributes of the COL element. I'm sure you need to use FOR XML clause in select statement. Keep in mind, this should work given any table passed in - so you don't know the names of the columns in advance.

Thanks for any help!

You can use information_schema.columns and for xml path like this to get the structure you want.

select
  'MyTable' as 'TABLE/@name',
  (select XMLCol as '*'
   from (select XMLCol
         from MyTable
           cross apply
            (select
               COLUMN_NAME as 'COL/@name',
               case COLUMN_NAME when 'FirstName' then FirstName end as COL,
               case COLUMN_NAME when 'LastName' then LastName end as COL
             from INFORMATION_SCHEMA.COLUMNS
             where TABLE_NAME = 'MyTable'
             for xml path(''), root('ROW'), type) as Row(XMLCol)
        ) as Rows
   for xml path(''), type) as 'TABLE'
for xml path('')

But in your case I see no other option than to build this dynamically.

declare @TableName varchar(50) = 'MyTable'

declare @ColList varchar(8000)
select @ColList = coalesce(@ColList+', ', '') + 'case COLUMN_NAME when '''+COLUMN_NAME+''' then '+COLUMN_NAME+' end as COL'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName

declare @SQL varchar(max) = 
'select
  ''_TABLENAME_'' as ''TABLE/@name'',
  (select XMLCol as ''*''
   from (select XMLCol
         from _TABLENAME_
           cross apply
            (select
               COLUMN_NAME as ''COL/@name'',
               _COLLIST_
             from INFORMATION_SCHEMA.COLUMNS
             where TABLE_NAME = ''_TABLENAME_''
             for xml path(''''), root(''ROW''), type) as Row(XMLCol)
        ) as Rows
   for xml path(''''), type) as ''TABLE''
for xml path('''')'

set @SQL = replace(@SQL, '_TABLENAME_', @TableName)
set @SQL = replace(@SQL, '_COLLIST_', @ColList)

exec (@SQL)

You can query the data dictionary for column names. Check out information_schema.columns.

Something like

select column_name
from information_schema.columns
where table_name = 'mytable'

will give you the column names.

I don't know if you have to use the format you have specified above. If a schema defining that format is your application's defined interface with other applications, then it probably shouldn't be tampered with.

Still, if you could use any XML format to serialize your table structure and data, then maybe it would be more practical to fetch your data into a .NET Dataset and just call

 yourDataset.WriteXml("[yourFileName].xml")

The structural information about the table and it's columns can be inferred from the resulting XML file, and it is very easy to pick up this file programmatically from another .NET app.