Re: XML and Postgres

On 3/17/05 2:53, "brew(at)theMode(dot)com" <brew(at)theMode(dot)com> wrote:

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

In these cases, you can use XML::Twig to break up the file into smaller
parts--it is quite easy and VERY fast.

Sean