[SOLVED] How to build a database from an XSD schema and import XML data

How to build a database from an XSD schema and import XML data

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

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

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.

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

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

Solution

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).

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 is a big topic and I'm not going to go over all of the bells and whistles here but basically you:

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).

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.

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