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.