Saturday, March 17, 2007

Create DDL from a DataSet XSD file

Recently I started dealing with a web service that came with a series of XSDs. We intend to store the data coming back from the web service, more or less in the form in which it arrived. That meant creating a series of database tables that would look very similar to the XSD. The XSD was long and complicated. I really, really didn't want to have to hand-create SQL tables from it. Surely, I thought, surely somebody has written something to create DDL from an XSD.

Well, yes, they have, but they want money for it, so I did it myself. I wrote an XSLT stylesheet that transforms a DataSet XSD into a series of DDL statements. I based it on a DataSet because the additional msdata: attributes incorporated in the DataSet and the DataSet designer let you specify auto-numbering for columns, designate one key as the primary key, and so on. Plus, the DataSet designer makes it easy to specify foreign keys and relationships. Turning the XSD into a DataSet just required some cutting and pasting, wrapping complex types with xs:element tags to create table definitions, and adding key fields. The DataSet designer was very nice about converting from one alias for the XSD namespace to another during a paste between documents.

I'm proud of it. It creates tables, declares primary keys, handles identity columns, and creates foreign key relationships. It supports derived simple types, drilling down to their base types and handling maxLength restrictions. Additional things that could be done with it (that I didn't need) include turning non-primary key key definitions into unique indexes, creating check constraints from some of the other restrictions put on derived types, and turning enumerations into lookup tables and usages of enumerations into references to the lookup tables.

This is the kind of thing that you don't need until you need it and is very helpful then. If people are interested in seeing some of it, let me know.

ETA: The XSLT is here.

12 Comments:

Blogger DonSchenck said...

I would like to add this utility to my bag of tricks.

6/12/2007 12:46 PM  
Blogger CodingFun said...

I appreciate your work. It would save me several days of work if I could have this.

10/12/2007 5:13 AM  
Blogger Shrom said...

What products ( besides XMLSpy ) provide generation of DDL from xsd?
Can you post your work?

10/22/2007 2:42 PM  
Blogger Deb said...

Can you post your work please?

10/23/2007 9:22 AM  
Blogger The X said...

Could you please post your work? I definitely could use tricks like you have described.

11/06/2007 2:30 PM  
Blogger Unknown said...

Could i have a look to your work??thanks

10/01/2009 3:15 AM  
Blogger Ann said...

I've posted it at here.

10/04/2009 9:31 AM  
Blogger Elvis said...

Could you please explain how to use this?

4/05/2010 9:03 PM  
Blogger Ann said...

I'd be happy to. Since I don't know what programming environment you're working in, I can only give general instructions. (If you want to write back and let me know more about your environment, I can try to be more specific.)

To use this script, you'll need a way to apply it to an XML file. The free tool Cooktop can do that. There are other programming tools and environments (like Eclipse) that have support for applying XSL scripts. If you're in the .Net environment, on the other hand, I can post a small program that uses C# and .Net to do it for you.

HTH

4/08/2010 1:58 PM  
Blogger kenboo said...

There's XSD2DB at Sourceforge that does the same thing. The utility written in C# loads XSD into DataSet, and iterate through ds.tables property, and then columns properties of a table. I've added Postgresql support for my needs.

10/06/2010 12:42 PM  
Blogger Ann said...

Thanks for your comments. I became aware of that project after posting mine. I've kept mine up because it accomodates some things that XSD2DB does not. Mine also predates it (it was written in late 2006, although I didn't post it until 2007) so at the time I needed this tool, there was nothing non-commercial available.

Thanks again for commenting! I'm glad that you have a tool that meets your needs!

10/08/2010 1:15 PM  
Blogger Unknown said...

select="ancestor::xs:element[@name][not(@msdata:IsDataSet)][xs:complexType[xs:sequence[xs:element[@type]] or xs:attribute[@type] or xs:simpleContent]]|$contents//xs:element[@type=current()/ancestor::*/@name]" />


I am not able to get my ancestor name. Therefore its not generating foreign key constraint.

Thanks you.

3/02/2011 7:17 PM  

Post a Comment

<< Home