Generate XML-formatted table schema from SQL Server 2005?

I was wondering how I might take a table schema in SQL Server and generate an XML document from it. The ideal would be if I passed in my database name (“SalesOrders”) and an XML doc comes back reading something like:

<table=”SalesOrders”> <columns> <name=”SalesOrderID”/> <datatype=”int”/> <allowNulls=”false”/> </name> <name=”DateOfSale”> <datatype=”DateTime”/> <allowNulls=”false”/> </name> </columns> </table>

You get the idea. Something along these lines, an XSD schema would be fine too. In the back of my head I think SQL Server has mechanisms for doing this but I'm not positive. Many thanks for your suggestions.


Something like the following would work. Also, note that your example XML is not well formed. I took the liberty of making it well formed.

declare @tableName varchar(255)

select @tableName = 'SalesOrders'

select (
select column_name,
when 'YES' then 'true'
else 'false'
end as is_nullable
from information_schema.columns [columns]
where table_name = @tableName
for xml auto, type
).query (' <table name="{sql:variable("@tableName")}">
for $column in /columns

<column name="{data($column/@column_name)}">
<dataType value="{data($column/@data_type)}"/>
<allowNulls value="{data($column/@is_nullable)}"/>


select @tableName as "@name",
select column_name as "@name",
data_type as "dataType/@value",
when 'YES' then 'true'
else 'false'
end as "allowNulls/@value"
from information_schema.columns
where table_name = @tableName
for xml path('column'), type
for xml path('table')

Both queries would produce the following:

<table name="SalesOrders">
<column name="SalesOrderID">
<datatype value="int"/>
<allowNulls value="false"/>
</column >
<column name="DateOfSale">
<datatype value="DateTime"/>
<allowNulls value="false"/>
</column >

As a side note:

Although it's usually a matter of taste when deciding on elements vs attributes in an XML structure, I would make dataType and allowNulls attribtes as opposed to elements, which seems more intuitive to me. So, the XML structure would look something like this:

<table name="SalesOrders">
<column name="SalesOrderID" datatype="int" allowNulls="false"/>
<column name="DateOfSale" datatype="DateTime" allowNulls="false"/>

The above queries can be easily modified to reflect this change.

How about

Select * From Information_Schema.Columns For XML Auto

Category:xml Time:2010-08-13 Views:1

Related post

Copyright (C), All Rights Reserved.

processed in 0.534 (s). 13 q(s)