如何从XSD模式构建数据库和导入XML数据 [英] How to build a database from an XSD schema and import XML data

问题描述

我有一个复杂的XSD模式和符合模式的数百个XML文件。

I have a complex XSD schema and hundreds of XML files conforming to the schema.

如何自动创建相关的SQL Server表来存储XML数据？

How do I automate the creation of related SQL Server tables to store the XML data?

我已经考虑使用 xsd.exe 工具从XSD模式创建C＃类，并让像Subsonic这样的东西

I've considered creating C# classes from the XSD schema using the xsd.exe tool and letting something like Subsonic figure out how to make a shiny database out of it, but not sure if it's the best way to approach it.

有没有人能够优雅地将XSD文件导入SQL Server？

Has anyone managed to elegantly import XSD files into SQL Server?

有很好答案的类似问题：如何从XSD文件创建数据库表？

A similar question with good answers: How can I create database tables from XSD files?

推荐答案

我建议您使用SQL Server集成服务，它与SQL Server 2008或2005（或数据转换服务，如果你卡住了2000）。

I suggest you use SQL Server Integration Services, which comes with SQL Server 2008 or 2005 (Or Data Transformation Services if your stuck with 2000).

不幸的是它不附带免费的Express版本的SQL Server，但是SQL Server Developer版本可以用于< $ 100，它有完整的SQL Server标准功能，并将满足您的需要。

Unfortunately it doesn't come with the free "Express" version of SQL Server, however SQL Server Developer edition can be had for < $100 which has the full SQL Server Standard functionality and would suit your needs.

SSIS是一个大主题，我不会去讨论所有的钟声和口哨

SSIS is a big topic and I'm not going to go over all of the bells and whistles here but basically you:

• 使用BIDS（商业智能开发工作室，SSIS附带的修改后的Visual Studio）创建一个新的SSIS项目


• 将新的数据流任务拖放到控制流表面，然后单击数据流选项卡。


• 将工具箱中的XML源然后配置XSD和XML文件位置。


• 将工具箱中的ADO.NET数据目标拖动到数据流上，并将XML源中的一个输出连接到输入ADO.NET目标。如果要基于从xml模式输出的数据创建新表，而不是在ADO.NET目标中指定连接管理器设置时使用现有的单击新建，并生成并执行相应的创建表声明。对于来自XML源的任何其他输出重复此操作（对于从模式生成的每个逻辑平面表，将有一个输出）。

• Create a new SSIS project using BIDS (Business Intelligence Development Studio, a modified Visual Studio that comes with SSIS)
• Drag a new Data Flow Task onto the Control Flow surface, then click the data flow tab.
• Drag an "XML source" from toolbox into data flow panel, and then configure the XSD and XML file locations.
• Drag an ADO.NET data destination from the toolbox onto the dataflow and connect one of the the outputs from the XML source to the input of the ADO.NET destination. If you want to create a new table based on the data output from the xml schema as opposed to using an existing one click on "New" when specifying the Connection Manager Settings in the ADO.NET Destination and it generate and execute the appropriate create table statement. Repeat this for any other outputs from the XML source (there will be one for each logical flat table generated from the schema).

将很可能需要使用其他数据转换对象首先转换数据之前它加载到SQL服务器，但这是它的一般要点。如果您需要为大量XML文件运行该过程，您可以将任务放在控制循环中，并使用变量设置XML文件位置。

You will most probably need to use other data transformation objects first to transform the data before it loaded into SQL server, but that is the general gist of it. If you need to run the process for a large amount of XML files you could put the task in a control loop and use a variable to set the XML file location.

MS在SSIS中使用XML源文档如下： http://msdn.microsoft.com/en-us/library/ms140277(v=SQL.100).aspx

The MS Documentation on using an XML source in SSIS is here: http://msdn.microsoft.com/en-us/library/ms140277(v=SQL.100).aspx

这篇关于如何从XSD模式构建数据库和导入XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！