Generate a normalized XML doc from SQL Server

I'm trying to generate a normalized XML document out of SQL server
that reflects the data
structure of a table.
Eg. This is what I would like to get
<table name='MtFeedback'>
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
I have tried two ways of and these are the results. The 1st technique
is close
select
1 as Tag,
null as Parent,
'' as [table!1],
null as [field!2!table-name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1],
i.TABLE_NAME as [field!2!table-name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table>
<field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
<field table-name="MtFeedback" name="Title" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="FirstName" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="Surname" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="insertedon" type="datetime"/>
</table>
The 2nd technique fails totally.
select
1 as Tag,
null as Parent,
i.TABLE_NAME as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
from
information_schema.columns i
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="categories"/>
<table name="categories"/>
<table name="products"/>
<table name="products"/>
<table name="products"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties">
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
Cheers David
declare @.tb sysname
set @.tb='MtFeedback'
select
1 as Tag,
null as Parent,
@.tb as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
i.TABLE_NAME as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = @.tb
FOR XML EXPLICIT
"muesliflakes" <muesliflakes@.yahoo.com.au> wrote in message
news:8deb82de.0406011916.1e8194b3@.posting.google.c om...
> I'm trying to generate a normalized XML document out of SQL server
> that reflects the data
> structure of a table.
> Eg. This is what I would like to get
> <table name='MtFeedback'>
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> I have tried two ways of and these are the results. The 1st technique
> is close
> select
> 1 as Tag,
> null as Parent,
> '' as [table!1],
> null as [field!2!table-name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1],
> i.TABLE_NAME as [field!2!table-name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table>
> <field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
> <field table-name="MtFeedback" name="Title" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="FirstName" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="Surname" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="insertedon" type="datetime"/>
> </table>
> The 2nd technique fails totally.
> select
> 1 as Tag,
> null as Parent,
> i.TABLE_NAME as [table!1!name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> from
> information_schema.columns i
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1!name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="categories"/>
> <table name="categories"/>
> <table name="products"/>
> <table name="products"/>
> <table name="products"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties">
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> Cheers David