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.

--------------Solutions-------------

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,
data_type,
case(is_nullable)
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
return

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

or

select @tableName as "@name",
(
select column_name as "@name",
data_type as "dataType/@value",
case(is_nullable)
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">
<columns>
<column name="SalesOrderID">
<datatype value="int"/>
<allowNulls value="false"/>
</column >
<column name="DateOfSale">
<datatype value="DateTime"/>
<allowNulls value="false"/>
</column >
</columns>
</table>

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">
<columns>
<column name="SalesOrderID" datatype="int" allowNulls="false"/>
<column name="DateOfSale" datatype="DateTime" allowNulls="false"/>
</columns>
</table>

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) pcaskme.com, All Rights Reserved.

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