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开发者_如何学Go=”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,
            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