将数据库表转换为XML模式文件

I am using SQL Server 2005. Is there any command or GUI tool (e.g. any menu/function from SQL Server management studio) to convert database table into XML schema file (.xsd)?

我正在使用SQL Server 2005.是否有任何命令或GUI工具(例如SQL Server管理工作室的任何菜单/功能)将数据库表转换为XML模式文件(.xsd)?

thanks in advance, George

乔治,提前谢谢

3 个解决方案

#1

I've found this. Give it a try

我发现了这个。试试看

Select CourseID, Name, CoursePrice
FROM CouseMaster.Course Course
FOR XML AUTO, XMLSCHEMA

#2

You can write to file like this:

您可以像这样写入文件:

bcp.exe "select top 0 * from (select 1 as iCol) as t for xml auto, xmlschema" queryout outfile.xsd -T -c

I'm Using the TOP 0 to exclude the xml of the actual query data since you only want the schema. The -c causes it to be plain character data in the output, use -w instead if you want utf-16 (unicode) output.

我使用TOP 0来排除实际查询数据的xml,因为您只需要架构。 -c使它成为输出中的普通字符数据,如果你想要utf-16(unicode)输出,请使用-w。

EDIT - and if you want to change the xml structure, look at PATH with FOR XML.

编辑 - 如果要更改xml结构,请使用FOR XML查看PATH。

#3

Declare @SQL nvarchar(1000) SET @SQL= 'bcp.exe '+ '"select * from yourdbname.yourschema.yourtablename for xml path (''record''), ROOT (''tabel'')"' +' queryout '+ 'c:\yourfilename.xsd' +' -w -r -t -SyourServerName -T'

声明@SQL nvarchar(1000)SET @ SQL ='bcp.exe'+'“select * from yourdbname.yourschema.yourtablename for xml path(''record''),ROOT(''tabel'')”'+' queryout'+'c:\ yourfilename.xsd'+' - w -r -t -SyourServerName -T'

print @SQL EXEC Master..xp_CmdShell @SQL

打印@SQL EXEC Master..xp_CmdShell @SQL

Replace allvalues starts with 'your', accordingly

因此,将所有值替换为“您的”

#1