By Oded


2008-09-26 10:04:26 8 Comments

I have a set of XSDs from which I generate data access classes, stored procedures and more.

What I don't have is a way to generate database table from these - is there a tool that will generate the DDL statements for me?

This is not the same as Create DB table from dataset table, as I do not have dataset tables, but XSDs.

8 comments

@user3043225 2013-11-27 19:32:31

The best way to create the SQL database schema using an XSD file is a program called Altova XMLSpy, this is very simple:

  1. Create a new project
  2. On the DTDs / Schemas folder right clicking and selecting add files
  3. Selects the XSD file
  4. Open the XSD file added by double-clicking
  5. Go to the toolbar and look Conversion
  6. They select Create structure database from XML schema
  7. Selects the data source
  8. And finally give it to export the route calls immediately leave their scrip schemas with SQL Server to execute the query.

Hope it helps.

@Chris Marisic 2014-09-23 14:00:05

As XmlSpy 2015 Create structure database from XML schema does not seem to be an option. The only relevant option under Conversion is to create an XML schema FROM the database.

@Govind 2015-03-17 06:45:16

@ChrisMarisic if you just open XML spy and check Convert Menu, it will show create an XML schema from database option only. What you have to do is, 1st open your xml schema file in xml spy then you can many options including Create DB structure from XML Schema. Hope this helps for others.

@Spoike 2009-04-20 15:20:56

There is a command-line tool called XSD2DB, that generates database from xsd-files, available at sourceforge.

@VilemRousi 2013-05-20 08:18:09

unfortunatelly it did not work for me. It threw an error - System.Data.DataException: DataSet Object doesn´t support list and union attributes of element simpleType.

@HGMamaci 2013-11-26 15:38:37

System.Data.DataException: Undefined data type: 'token'.

@jaccso 2014-08-14 18:31:58

Doesn't work for me, neither with a very simple complex type. MsSql 2012

@settwi 2017-06-27 15:42:17

I realize this question is pretty dead, but I did some digging and found that the reason XSD2DB throws this type of exception is because it's using DataSet to interpret the XML Schema, when it should be using XmlSchemaSet or XmlSchema to compile and manipulate the schema file. The code is pretty dependent on the DataSet implementation, however, so I didn't delve into modifying it to be functional.

@BeWarned 2008-11-18 01:08:42

XML Schemas describe hierarchial data models and may not map well to a relational data model. Mapping XSD's to database tables is very similar mapping objects to database tables, in fact you could use a framework like Castor that does both, it allows you to take a XML schema and generate classes, database tables, and data access code. I suppose there are now many tools that do the same thing, but there will be a learning curve and the default mappings will most like not be what you want, so you have to spend time customizing whatever tool you use.

XSLT might be the fastest way to generate exactly the code that you want. If it is a small schema hardcoding it might be faster than evaluating and learing a bunch of new technologies.

@uday 2008-11-18 00:45:55

hyperjaxb (versions 2 and 3) actually generates hibernate mapping files and related entity objects and also does a round trip test for a given XSD and sample XML file. You can capture the log output and see the DDL statements for yourself. I had to tweak them a little bit, but it gives you a basic blue print to start with.

@Shawn 2008-09-27 17:23:58

Might take a look at the XSD tool in visual studio 2k8... I have created a relational dataset from an xsd and it might help your out somehow.

@Yahoo Serious 2013-07-04 11:29:45

One can use xsd.exe to generate the schema, but you must add relationships yourself, see the XML Bulk Load Examples on MSDN, or the example in this question. (I seem to recall fixing some data types, but maybe that was generating the XSD from the XML.)

@Yahoo Serious 2013-07-04 11:31:24

As I once mentioned here the directory for xsd.exe seems to have changed on Windows 7. I have it in many Microsoft SDKs directories, so it does not seem to depend on Visual Studio anymore(?). One directory would be: C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools.

@Robert Gould 2008-09-26 10:59:36

I use XSLT to do that. Write up your XSD then pass your data models through a hand written XSLT that outputs SQL commands. Writing an XSLT is way faster and reusable than a custom program /script you may write.

At least thats how I do it at work, and thanks to that I got time to hang out on SO :)

@Kiquenet 2017-03-06 09:04:01

Have you any sample using XSLT ? stackoverflow.com/help/how-to-answer

@JeeBee 2008-09-26 10:54:03

Create a Java Model using Axis wsdl2java (which can take in .xsd files).

Use a database generation tool for Java that takes in a Java Model. Surely something like Hibernate can do this? I wrote my own tool (takes a couple of days, also generates CRUD code in Java too) to save myself time at work, maybe this would be a nice personal project?

Or just do it manually so that you can check everything is correct and good! Database tools are good enough now that you can zip through creating tables for a model without too many problems.

@S.Lott 2008-09-26 10:29:28

Commercial Product: Altova's XML Spy.

Note that there's no general solution to this. An XSD can easily describe something that does not map to a relational database.

While you can try to "automate" this, your XSD's must be designed with a relational database in mind, or it won't work out well.

If the XSD's have features that don't map well you'll have to (1) design a mapping of some kind and then (2) write your own application to translate the XSD's into DDL.

Been there, done that. Work for hire -- no open source available.

@jaccso 2014-08-14 18:42:02

If you have an enterprise license..

@Chris Marisic 2014-09-23 15:07:28

XML Spy 2015 didn't help me at all, XSD2DB did though.

Related Questions

Sponsored Content

26 Answered Questions

[SOLVED] Why not inherit from List<T>?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

8 Answered Questions

[SOLVED] How does database indexing work?

27 Answered Questions

20 Answered Questions

9 Answered Questions

[SOLVED] How to 'insert if not exists' in MySQL?

7 Answered Questions

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

17 Answered Questions

8 Answered Questions

[SOLVED] How to shrink/purge ibdata1 file in MySQL

Sponsored Content