Thread: Re: Postgres+XML

Jan Szumiec wrote:
 >
 > Does PG allow you to store XML documents as DOM documents?
 > Is there such data type? If not, is it very hard to extend
 > the type system to include a binary representation of an
 > XML document?

There are a few ways.  I like the stuff in "contrib/xml2".

There is also the "pgsql" project at
http://gborg.postgresql.org/project/xpsql/projdisplay.php
that I think provides similar functionality.

 > What I'm trying to do is the following:
 >
 > SELECT AVERAGE(xpath_query('/ds/item[@name=\'left\']')
 >  GROUP BY language;

I think the example below shows the features you're
interested in using the 'xml2' package from contrib.

fli=# create table xmltest (xml text);
CREATE TABLE
fli=# insert into xmltest values('<a><b id="one">1</b></a>');
INSERT 218847847 1
fli=# insert into xmltest values('<a><b id="two">2</b></a>');
INSERT 218847848 1
fli=# select sum(xpath_number(xml,'/a/b[@id="one"]')) from xmltest;
  sum
-----
    1
(1 row)

fli=# select sum(xpath_number(xml,'/a/b')) from xmltest;
  sum
-----
    3
(1 row)

You can see another example of it here:
http://www.throwingbeans.org/tech/postgresql_and_xml.html
but the README in Postgresql's source distribution is
probably the best reference.