Re: Storing XML in PostgreSQL - Mailing list pgsql-hackers

In article <4.2.2.20010724150449.00a9ea90@192.168.0.67>,
jm.poure@freesurf.fr (Jean-Michel POURE) wrote:
> Hello friends,
> 
> What is the best way to parse and store an XML document in PostgreSQL? I
> would like to store fwbuilder (http://www.fwbuilder.org) objects in 
> PostgreSQL.
> 

I think the best way depends on what you're trying to achieve with the
document once you have it in the database. One approach is to have tables
for  elements, attributes and cdata and use an XML parser to insert
appropriate database records.

I have used a schema such as the following- in the cdata table "element" is
the  ID of the containing element, and itempos is just an integer used to
order the  entries. I used this with a bit of java which hooks up to the
Lark parser (using SAX) to do the parsing and fires off INSERT queries 
through the jdbc driver. 

CREATE SEQUENCE cdata_seq; 
CREATE SEQUENCE attribute_seq; 
CREATE SEQUENCE element_seq; 

CREATE TABLE element (      document integer,        element integer not null PRIMARY KEY      default
nextval('element_seq'),name text, parent integer, itempos      integer      );
 

CREATE TABLE attribute (      document integer,        attribute integer not null default
nextval('attribute_seq'),name text, value text, element integer,      itempos integer      );
 


CREATE TABLE cdata (      document integer,        cdata integer not null default      nextval('cdata_seq'), value
text,element integer, itempos integer      );
 

In my example, I was interested in selecting all the cdata content 
of a <type> tag immediately contained within a <feature> tag path.

The easiest solution is to create a view, which can then be queried to 
find  all cases where, for example, feature type = 'Ditch'.

CREATE VIEW featuretype AS featuretype
SELECT c.document,c.value
FROM cdata c, element e, element e1
WHERE c.element = e.element 
AND   e.parent = e1.element
AND   e.name = 'type'
AND  e1.name = 'feature'
AND   c.document = e.document
AND   e.document = e1.document;

if you are interested I can provide the very basic (java) code I used for 
this.

OR, depending on what these fwbuilder objects involve,  you can of 
course just store XML documents in fields of type text (especially if 
you use 7.1 which has TOAST, so you can store long documents). IT's 
not difficult to hook up a parser (I'm using expat) to a PostgreSQL 
function written in C and parse on the fly.

I haven't really finished that code, but after I've commented it, I can 
certainly post it if anyone is interested. It does work, but probably
needs some tidying. It really wasn't difficult to write the functions 
though. In fact, I've been surprised by how easy it is to write 
PostgreSQL C functions...

Please contact me if you have any questions -I've been away for a bit
so haven't worked on that code for a couple of weeks -I'm hoping to 
get back into it soon.

Regards

John