C# 如何从 XML 架构创建 SQL Server 表架构？（使用 .NET 和 Visual Studio 2008）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1009154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create SQL Server table schema from a XML schema? (with .NET and Visual Studio 2008)
提问by Jader Dias
I have a XML schema, and I know that "xsd.exe" can generate the C# code for it. But I want to know if is possible to automatically create the MS SQL Server 2005+ tables from the XSD, with the help of this or other tools.
我有一个 XML 模式，我知道“xsd.exe”可以为它生成 C# 代码。但是我想知道是否可以在此工具或其他工具的帮助下从 XSD 自动创建 MS SQL Server 2005+ 表。
BTW I didn't get what the C# code generated by "xsd.exe" is worth for.What's the difference between code generated by CodeXS and xsd.exe?
顺便说一句，我没有得到“xsd.exe”生成的 C# 代码的价值。CodeXS 和 xsd.exe 生成的代码有什么区别？
采纳答案by Pratixa
You can use the XSD2DB utility
您可以使用 XSD2DB 实用程序
This is the Example xsd2db.exe -f true -l [Server Name] -n [Database Name] -s D:\po.xsd -t sql
这是示例 xsd2db.exe -f true -l [服务器名称] -n [数据库名称] -s D:\po.xsd -t sql
Link for Help http://xsd2db.sourceforge.net/
回答by TheTXI
As long as you can successfully parse your XML schema, you should be able to create the appropriate database scripts and execute them which will create your tables.
只要您可以成功解析您的 XML 模式，您就应该能够创建适当的数据库脚本并执行它们来创建您的表。
回答by Brent Ozar
Disclaimer: I haven't done this myself, but I bookmarked these links a little while ago when I was thinking about doing this. This guy's T-SQL is usually brilliant, so I'd recommend it highly:
免责声明：我自己没有这样做过，但不久前我在考虑这样做时将这些链接加入了书签。这家伙的 T-SQL 通常很出色，所以我强烈推荐它：
http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/06/04/Extract-XML-structure-automatically-part-2.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/06/04/Extract-XML-structure-automatically-part-2.aspx
回答by Brian Genisio
BTW I didn't get what the C# code generated by "xsd.exe" is worth for.
顺便说一句，我没有得到“xsd.exe”生成的 C# 代码的价值。
I am assuming what you mean is "I don't understand how the generated code is useful"
我假设你的意思是“我不明白生成的代码是如何有用的”
The purpose of the code it generates is to serialize using the Microsoft serialization subsystem in .NET. If you create a new XmlSerializer(typeof(GeneratedType)), you can then call Serialize() and Deserialze() on it to go to/from Xml and objects.
它生成的代码的目的是使用 .NET 中的 Microsoft 序列化子系统进行序列化。如果您创建了一个新的 XmlSerializer(typeof(GeneratedType))，那么您可以在其上调用 Serialize() 和 Deserialze() 以在 Xml 和对象之间来回切换。
In a more complicated code generator, such as CodeXS, it becomes even easier, as they generate helpers for you: GeneratedType.FromXML(Stream/String) to deserialize and myGeneratedType.Xml to serialize.
在更复杂的代码生成器（例如 CodeXS）中，它变得更加容易，因为它们会为您生成帮助程序：用于反序列化的 GeneratedType.FromXML(Stream/String) 和用于序列化的 myGeneratedType.Xml。
These generated classes allow you to work off a published schema, and have total confidence that any XML generated that meets the schema will parse and be generated using these types. You don't need to do any work to get the data out of the XML (ie no XML DOM access) and you don't need to think twice about generating XML that is compliant with your schema. It just works :)
这些生成的类允许您处理已发布的模式，并且完全相信生成的任何符合模式的 XML 都将使用这些类型解析和生成。您不需要做任何工作来从 XML 中获取数据（即没有 XML DOM 访问），并且您不需要考虑生成符合您的模式的 XML。它只是有效:)
回答by StepUp
I know that this is an old topic, however it was really helpful to me to know how it can be done. Maybe it would be helpful to others.
我知道这是一个古老的话题，但是知道如何完成对我来说真的很有帮助。也许这对其他人有帮助。
More advanced examples can be seen at Microsoft page.
So, it is possible to create manually SQL table from XSD. For example, we have the following xml:
因此，可以从 XSD 手动创建 SQL 表。例如，我们有以下 xml：
<?xml version="1.0" encoding="UTF-8"?>
<foo>
<row>
<CAR_NUM>624</CAR_NUM>
<CAR_ORDER>1</CAR_ORDER>
</row>
<row>
<CAR_NUM>623</CAR_NUM>
<CAR_ORDER>2</CAR_ORDER>
</row>
<row>
<CAR_NUM>681</CAR_NUM>
<CAR_ORDER>3</CAR_ORDER>
</row>
<row>
<CAR_NUM>625</CAR_NUM>
<CAR_ORDER>4</CAR_ORDER>
</row>
<row>
<CAR_NUM>680</CAR_NUM>
<CAR_ORDER>5</CAR_ORDER>
</row>
</foo>
So out XSD will look like this:
所以 XSD 将如下所示：
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="row" sql:relation="CAR"
sql:key-fields="CAR_NUM">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CAR_NUM" type="xsd:integer" />
<xsd:element name="CAR_ORDER" type="xsd:integer" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
And SQL table will look like this:
SQL 表将如下所示：
CREATE TABLE CAR (
CAR_NUM INT ,
CAR_ORDER INT
GO
And then you can use Interop.SQLXMLBULKLOADLiblibrary to load data into SQLServer:
然后您可以使用Interop.SQLXMLBULKLOADLib库将数据加载到 SQLServer 中：
try
{
var xmlFileName = @".your address here..\GetInformReplyEdited.xml";
var xsdScheme = @".your address here..\scheme.xsd";
var connString = "Provider=sqloledb;server=yourServer;database=Test;integrated security=SSPI";
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class
{
ConnectionString = connString,
ErrorLogFile = "error.xml",
KeepIdentity = false
};
objBL.Execute(xsdScheme, xmlFileName);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
