是否从SQLServer2005生成XML格式的表架构?

像下面这样的方法也行。另外,请注意,您的示例XML的格式不是很好。我冒昧地把它写得很好。

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>            
')

或

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')

两个查询都将产生以下结果:

<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>

作为旁注:

dataType 和 allowNulls 属性,而不是元素,这对我来说更直观。因此,XML结构如下所示:

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

怎么样

Select * From Information_Schema.Columns For XML Auto