Convert Microsoft Access .XSD file to SQL table create statement

Here’s a quick-and-dirty XSLT script for transforming a Microsoft Access XML schema file into an SQL table create statement. Being quick and dirty, you need to edit the resulting statement, but it will get you most of the way there.

I have a need to generate some SQL tables for some data exported from Microsoft Access as XML files. Unless you ask it not to, Microsoft Access also generates XML schema files for each XML export, which can be quite handy when you only get to see the exported files and not the original database. Once I have SQL table create statements, I can easily generate some PHP classes that map to the tables, and XML loaders for the data, but I need those table statements first.

Enter XSLT. Since the schema files are XML, it’s not too hard to create an XSL transformation script to process them. This one is so quick and dirty that it leaves a trailing comma on the last column definition, but you’ll probably want to edit the SQL anyway so I didn’t go the extra yards to clean that up. Note that my target database is MySQL (it’s a Classic ASP to WordPress conversion), so if you need a different target you’ll need to modify it. Here’s the XSLT:

<?xml version="1.0" encoding="utf-8" ?>

<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:od="urn:schemas-microsoft-com:officedata">

  <xsl:output method="text"/>

  <xsl:template match="*/*/xsd:maxLength">
    <xsl:value-of select="@value"/>
  </xsl:template>

  <xsl:template match="xsd:element[@od:sqlSType]">
    <xsl:value-of select="@name"/>
    <xsl:choose>

      <xsl:when test="@od:sqlSType='int'">
        <xsl:text> integer</xsl:text>
      </xsl:when>

      <xsl:when test="@od:sqlSType='bit'">
        <xsl:text> tinyint</xsl:text>
      </xsl:when>

      <xsl:when test="@od:sqlSType='double'">
        <xsl:text> double</xsl:text>
      </xsl:when>

      <xsl:when test="@od:sqlSType='money'">
        <xsl:text> decimal(15,2)</xsl:text>
      </xsl:when>

      <xsl:when test="@od:sqlSType='datetime'">
        <xsl:text> datetime</xsl:text>
      </xsl:when>

      <xsl:when test="@od:sqlSType='nvarchar'">
        <xsl:text> varchar(</xsl:text>
        <xsl:apply-templates select="*/*/xsd:maxLength"/>
        <xsl:text>)</xsl:text>
      </xsl:when>

      <xsl:when test="@od:sqlSType='ntext'">
        <xsl:text> text</xsl:text>
      </xsl:when>

    </xsl:choose>

    <xsl:if test="@od:autoUnique='yes'">
      <xsl:text> auto_increment</xsl:text>
    </xsl:if>

    <xsl:if test="@od:nonNullable='yes'">
      <xsl:text> not null</xsl:text>
    </xsl:if>

    <xsl:text>,
    </xsl:text>
  </xsl:template>

  <xsl:template match="/xsd:schema/xsd:element[@name!='dataroot']">
    <xsl:text>create table </xsl:text>
    <xsl:value-of select="@name"/>
    <xsl:text> (
    </xsl:text>
    <xsl:apply-templates />
    <xsl:text>);
    </xsl:text>
  </xsl:template>

  <!-- suppress text nodes -->
  <xsl:template match="text()"></xsl:template>

</xsl:stylesheet>

To use it, you need an XSLT processor. From Linux, I use the xsltproc command:

xsltproc xsd2sql.xsl the-table.xsd > the-table.sql

Job is done in a hurry, so that I can get back to the real job! (that means that if this isn’t polished enough for you, please feel free to give it some of your own polish and stick it up in Gist or something!)

Facebooktwittergoogle_plusredditlinkedinmailFacebooktwittergoogle_plusredditlinkedinmail