x

XSD to DDL

Hi,

I have a task to import 30 different types of XML-documents into a database. The data should be stored in a way that the XML files can be easily generated from the database.

For each different type of XML-document, I have an XSD, with some included common XSD-documents. The XSD-files are nested (1.xsd includes Common1.xsd which includes Common2.xsd etc). The XSD-files contains both SimpleType- and ComplexType-definitions.

I'd like to avoid handcrafting the DDL and instead have the DDL generated from the XSD-files. Does anyone have experience from doing that?

Here's an example on how it could look.

1.xsd

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://my.namespace.com/xsd1"
    elementFormDefault="qualified"
    version="2.1">
    <xs:annotation>
       <xs:documentation>
         Some information
       </xs:documentation>
    </xs:annotation>
    <xs:include schemaLocation="Common/Common1.xsd"/>
    <xs:element name="Car">
       <xs:complexType>
         <xs:sequence>
          <xs:element name="BasicInformation" type="VehicleInformation"/>
          <xs:element name="Price" type="DecimalType" nillable="true"/>
          <xs:element name="Colour" type="xs:string"/>
          <xs:any namespace="##targetNamespace" processContents="lax" minOccurs="0" maxOccurs="unbounded"/>
         </xs:sequence>
       </xs:complexType>
    </xs:element>
</xs:schema>

2.xsd

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://my.namespace.com/xsd2"
    elementFormDefault="qualified"
    version="2.1">
    <xs:annotation>
       <xs:documentation>
         Some information
       </xs:documentation>
    </xs:annotation>
    <xs:include schemaLocation="Common/Common1.xsd"/>
    <xs:element name="Bike">
       <xs:complexType>
         <xs:sequence>
          <xs:element name="BasicInformation" type="VehicleInformation"/>
          <xs:element name="Height" type="DecimalType" nillable="true"/>
          <xs:element name="Manufacturer" type="xs:string"/>
          <xs:any namespace="##targetNamespace" processContents="lax" minOccurs="0" maxOccurs="unbounded"/>
         </xs:sequence>
       </xs:complexType>
    </xs:element>
</xs:schema>

Common/Common1.xsd

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://my.namespace.com/Common/Common1"
    elementFormDefault="qualified"
    version="2.1">
    <xs:annotation>
       <xs:documentation>
         Some information
       </xs:documentation>
    </xs:annotation>

<xs:simpleType name="DecimalType">
         <xs:restriction base="xs:decimal">
             <xs:totalDigits value="15"/>
             <xs:fractionDigits value="2"/>
         </xs:restriction>
</xs:simpleType>

    <xs:element name="VehicleInformation">
       <xs:complexType>
         <xs:sequence>
          <xs:element name="Age" type="DecimalType" nillable="true"/>
          <xs:any namespace="##targetNamespace" processContents="lax" minOccurs="0" maxOccurs="unbounded"/>
         </xs:sequence>
       </xs:complexType>
    </xs:element>
</xs:schema>
more ▼

asked Jan 13 '12 at 03:53 AM in Default

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

It should be possible to write a CLR assembly which will process the XSD using some XSLT or T4 implementation and generate appropriate DDL.

Unfortunately in case of your XSDs, you have <xs:any> elements in the sequences so the XML file based on that XSD can have more attributes than defined in the XSD. So you are not able to generate the DDL only from XSD to be able to import all XML files based on that XSD as each of that XML can have different set of attributes.

So in that case to generate a proper DDL you will need XSD and also an XML file to be imported. Then you should validate the XML that it matches the XSD and then generate the DDL based on XSDs and XML. For sure possible but probably a lot of coding in .NET (C# or VB.NET).

There is also a [XSD2DB][1] project on SourceForge which is a command line utility which is able to convert Microsoft ADO.NET compatible DataSet Schema File (XSD) into a DDL. But your XSDs seems not to be a ADO.NET XSDs. And especially as I have mentioned the biggest problems are the elements.

[1]: http://xsd2db.sourceforge.net/
more ▼

answered Jan 16 '12 at 02:14 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

Thanks for your answer!

I've been thinking about the xs:any elements, and they seem redundant to me when I also examine the XML-files that I've received (I've checked hundreds of them without finding anything that differs from the XSD). If an XML-file shows up, which includes elements not explicitly defined, it's something I don't want in my database anyway. So for the xs:any problem - it can be disregarded.

I looked into XSD2DB but it can't handle include-files in the XSD.

I've also looked into generating the tables using SSIS, but then if I go ahead with 1.xsd, I get a VehicleInformation-table with a foreign key to the Car-table. That won't do it for me, since VehicleInformation is a common table which is shared between all 30 main XSD-files involved.

I tried with XML Spy, but with no luch there either - it won't generate key columns.

My .NET and XML/XSLT skills are too limited to solve this programatically. So I've solved it by handwriting the DDL, and will take care of my (probable) mistakes as I create the import programs.
Jan 16 '12 at 10:34 PM Magnus Ahlkvist

If you can ignore the <xs:any> and use only the hard coded elements, than for sure it will be possible by T4 implementation - there should be no issue even with nested XSDs, however for sure it will require knowledge of .NET and T4 implementation.

I'm not also a great expert on XSLT (wrote a few simpler XML to HTML XSLTs) so do not know whether it is capable to handle the nested XSDs.
Jan 17 '12 at 01:28 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x136
x4

asked: Jan 13 '12 at 03:53 AM

Seen: 1881 times

Last Updated: Jan 13 '12 at 04:44 AM