0

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...

  • You might like to look here - possibly Oracle specific (mentions PL/SQL) - but, hey, that's F/LOSS for you :-) – Vérace Nov 18 '16 at 19:34
  • You will need to write a XSLT transformation that changes the XML (that you did not show us) to a SQL script that creates the tables – a_horse_with_no_name Nov 18 '16 at 22:48
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 :)

  • Thanks for the tip. ...I've been scratching my head with this for some time... I don't see any commands to do this; can you please be more specific? This looks like it's a source code management tool for database schemas - didn't see anything about XML in there! – Richard T Nov 19 '16 at 23:30
  • You can run it directly against your db - skipping SQL. Or use the updateSQL command - I'll update with a sample when I'm near a Computer... see: liquibase.org/documentation/maven/maven_updatesql.html – cohenjo Nov 20 '16 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.