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[(at)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[(at)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.