SQL to generate XML of table data

Question

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>
    <COL 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!

Mikael Eriksson Mikael Eriksson · Accepted Answer · 2011-04-07T08:10:10

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)

SQL to generate XML of table data

3 Answers