How to create SQL Server table schema from a XML schema? (with .NET and Visual Studio 2008)

Question

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.

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?

Answer 1

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.

Answer 2

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:

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

Answer 3

BTW I didn't get what the C# code generated by "xsd.exe" is worth for.

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.

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.

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

Answer 4

You can use the XSD2DB utility

This is the Example xsd2db.exe -f true -l [Server Name] -n [Database Name] -s D:\po.xsd -t sql

Link for Help http://xsd2db.sourceforge.net/