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.