How do I easily create the DDL statements for a schema represented by the contents of an XML file?

Question

I have been given an xml file and it's pretty obvious in looking at the contents that it contains an inherent schema. What I'd like to do is run a program against it that outputs the schema represented by the contents, then create said schema in / as a database (in Postgres), and then populate said database so I can run SQL queries against the data.

I figured that this has to be a commonly desired capability, but I've never needed to do this before and am not sure what already exists.

I work in Postgres, and it has many XML related tools, so I figure that's a good place to look but so far haven't found it.

Added information:

Doing some digging, I found that the people who gave us the xml file also have an "xml schema file" that matches, and I've gotten a copy. It's on the web here for those that care.)

OK, so step one was satisfied; get to an "xml schema."

Now I need to convert that into an SQL schema...

Answer 1

have a look at liquibase - it's a great tool, and sounds like what you are looking for. You could use it to create SQL statements using the updateSQL command or apply changes directly to the DB.

for example:

java -jar liquibase.jar \
    --driver=oracle.jdbc.OracleDriver \
    --classpath=jdbcdriver.jar \
    --url=jdbc:oracle:thin:@localhost:1521:oracle \
    --username=scott \
    --password=tiger \
    updateSQL > /tmp/script.sql

will output the required changes to the scripts.sql file.

It supports many db servers including Postgres, and you can model your schema in XML, JSON or any way you like.
See: XML changeset

<changeSet id="1" author="nvoxland">
    <createTable tableName="person">
        <column name="id" type="int" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="firstname" type="varchar(50)"/>
        <column name="lastname" type="varchar(50)">
            <constraints nullable="false"/>
        </column>
        <column name="state" type="char(2)"/>
    </createTable>
</changeSet>

It's open source and actively maintained.

We've been using it on multiple projects and dbs - highly recommended :)