Re: XML and Postgres

On Wed, 16 Mar 2005, Greg Lindstrom wrote:

> I am fairly new to postgres, but have been coding for over 20 years and
> would like to know what, if anything, is available to post data in XML
> format to postgres.

Hopefully someone can tell you of a direct solution, but I can tell you my
experiences using perl.

I had to synchronize an on site office database (File Maker Pro) with a
webserver database.  File Maker Pro could output in XML and that's way the
DBA wanted to do it.  He set it up so that it ftped the data in XML to the
web server and I use cron to start a perl script and import the data into
the webserver DB.

There are several XML perl modules available but all the ones I tried had
the same flaw - they grabbed the entire XML file and loaded it into a huge
variable in memory.  The XML file they wanted to upload was so big it used
up all the available memory and the perl script failed.

I had to write a parsing routine that churns through the XML file and
loads the data for one record, then dumps it into the database before
going back for the next record - it's much more memory efficient, albeit
slow - no grouped transactions, but that's all right - it runs in the
middle of the night.

I hesitate to post it since it may not work with all XML data files, I
wrote and tested it for MY particular datafile.  In particular it may not
work for empty elements ( like <data /> ). I'll be glad to share it and
explain how it works if you need it.

I confess the webserver DB in this case is mySQL (it pre-existed me on
this project), but since I used the perl DBI (Database Interface Library)
it will work with postgreSQL just as easily.

Hope this helps if nobody steps forward with a better solution.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================