By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,760 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,760 IT Pros & Developers. It's quick & easy.

Generate a normalized XML doc from SQL Server

P: n/a
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 Dave
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
oj
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" <mu**********@yahoo.com.au> wrote in message
news:8d**************************@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 Dave

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.