Создать схему таблицы в формате XML из SQL Server 2005?
Мне было интересно, как я могу взять схему таблицы в SQL Server и сгенерировать из нее XML-документ. Идеальным было бы, если бы я передал имя своей базы данных («SalesOrders»), и документ XML возвращается, читая что-то вроде:
<table=”SalesOrders”>
<columns>
<name=”SalesOrderID”/>
<datatype=”int”/>
<allowNulls=”false”/>
</name>
<name=”DateOfSale”>
<datatype=”DateTime”/>
<allowNulls=”false”/>
</name>
</columns>
</table>
Вы поняли идею. Что-то в этом роде, XSD-схема тоже подойдет. В глубине души я думаю, что в SQL Server есть механизмы для этого, но я не уверен. Большое спасибо за ваши предложения.
2 ответа
Что-то вроде следующего могло бы работать. Также обратите внимание, что ваш пример 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>
В качестве примечания:
Хотя обычно выбор элементов и атрибутов в структуре XML - дело вкуса, я бы сделал 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