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?

Solution 1

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/

Solution 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

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

Comments

[Jader Dias]

But how? Writing the script manually? There isn't an automatic way to do that?

[TheTXI]

Jader Dias: I don't know, but look at it this way...whatever automatic way there is would have had to have been written by someone at some point in time. If there isn't an automatic solution already out there, write your own?

[John Saunders]

@TXI: There doesn't have to be an automatic way to map, because there doesn't have to be a mapping. XML is not relational.

[John Saunders]

@Jader: does this have to work for all valid XML schemas? If so, then you're out of luck. XML can represent some things that cannot be represented in a relational database. If you limit yourself to those schemas that can map to relational, then it's not that hard. In fact, consider the schemas created by the DataSet Designer in VS2008. It will cheerfully ignore certain changes you make by hand, then rewrite them to match the nearest relational equivalent. If you had actually meant what you wrote, you'd be out of luck.

[Jader Dias]

Thanks, I used CodeXS generated code, and it seems to me much simpler and easier to use than "xsd.exe" generated code.

[dmcgill50]

Is there a good tutorial or place that I can download CodeXS? Is it still maintained?