Re: XML and Postgres

On Mar 17, 2005, at 12:36 PM, brew(at)theMode(dot)com wrote:

>
> Greg.....
>
>> 1. Do I understand correctly that using one of these tools I can
>> hand my
>> xml-formatted data file to postgres and it will be inserted into
>> the
>> database?
>
> Hopefully for you somebody else might know of something like that!
>
> Sorry to tell you, but I'm handling it manually. The perl program just
> serves as the bridge between the two databases. I had to manually
> normalize the database that I was going to pump the data into, then I
> had
> to write a bridging program to parse the data out of the XML file,
> loading
> each piece of data into a variable, then inserting or updating the
> proper
> table in the database with it.
>
> It's not the automatic solution you sound like you are hoping for.
> That's what I meant by writing 'hopefully somebody else can give you a
> direct solution'.
>
> Good luck finding what you need, maybe somebody else knows how to make
> postgreSQL handle XML data directly or knows of a database that will
> (and
> still be fast, etc.).

Unfortunately, I, like Brew, do it "by hand". There are modules for
xml/dbi connection, but I haven't used them. In practice, if you know
the database structure from which your data is coming, you can recreate
it easily enough. Then, it is just a question of parsing the XML (also
relatively trivial) and them making a loader using DBI (again, fairly
straightforward). It sounds like you data is coming out fairly
normalized already, so the process shouldn't require too much data
munging. If you need to munge, you can always load to a "loader"
schema and then use triggers to change the data into another format in
the "working" schema. As for querying your database, you could look at
contrib/xml, but that is only if you load straight XML. You will
probably load into regular tables for a more "standard" solution, so
you would do your queries in SQL. In case you want to look at more
automated solution, you might check out:

http://www.xml.com/pub/a/2000/12/13/perlxmldb.html

For a total different approach, there are a number of XML databases and
drivers:

http://xml.apache.org/xindice/
http://search.cpan.org/author/GSEAMAN/XML-DB/lib/XML/DB/Database/
Xindice.pm
http://search.cpan.org/author/GSEAMAN/XML-DB/lib/XML/DB/Database.pm
http://search.cpan.org/author/GSEAMAN/XML-DB/lib/XML/DB/Database/
Exist.pm

I haven't used them at all and have no idea how well they scale, etc.

Sean