PostgreSQL and XML support

On 9/6/05, Andrey Fomichev <fomichev@ispras.ru> wrote:

- Are there any of you who need to store and query XML data?

- Do you already use PostgreSQL to store XML data or you are

just thinking about it?

The project I'm currently working on uses XML for both data extraction from
Postgres and as a means to transmit data to Postgres for storage.

- If you store XML data in PostgreSQL, how do you do it? What tool

do you use?

We're using Oracle's XSQL servlet (
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10794/adx09xsq.htm)
with Apache tomcat (http://jakarta.apache.org/tomcat/) as its servlet
container. The only change we've made to XSQL is the addition of a custom
tag (<xsql:pg-func>) in order to make use of Postgres functions returning
REF CURSOR. Aside from that, we're using XSQL out-of-the-box.

Granted, we're not really manipulating XML within the database, rather we're
using XML as a sort of database-neutral interface.

- Do you need some advanced query capabilities? Like XQuery, XPath 2.0

No.

- Do you need some advanced update capabilities? Like node-level updates

No.

I have XML support in PostgreSQL in my Todo
http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo
Hopefully, we'll have something for 8.2

Oleg
On Tue, 6 Sep 2005, Doug Bloebaum wrote:

On 9/6/05, Andrey Fomichev <fomichev@ispras.ru> wrote:

- Are there any of you who need to store and query XML data?

- Do you already use PostgreSQL to store XML data or you are

just thinking about it?

The project I'm currently working on uses XML for both data extraction from
Postgres and as a means to transmit data to Postgres for storage.

- If you store XML data in PostgreSQL, how do you do it? What tool

do you use?

We're using Oracle's XSQL servlet (
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10794/adx09xsq.htm)
with Apache tomcat (http://jakarta.apache.org/tomcat/) as its servlet
container. The only change we've made to XSQL is the addition of a custom
tag (<xsql:pg-func>) in order to make use of Postgres functions returning
REF CURSOR. Aside from that, we're using XSQL out-of-the-box.

Granted, we're not really manipulating XML within the database, rather we're
using XML as a sort of database-neutral interface.

- Do you need some advanced query capabilities? Like XQuery, XPath 2.0

No.

- Do you need some advanced update capabilities? Like node-level updates

No.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Thank you Doug, Oleg for your responses.
I hoped there would be much more answers,
but... thanks anyway

Friendly,
Andrey

-----Original Message-----
From: Oleg Bartunov [mailto:oleg@sai.msu.su]
Sent: Tuesday, September 06, 2005 11:54 PM
To: Doug Bloebaum
Cc: Andrey Fomichev; pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL and XML support

I have XML support in PostgreSQL in my Todo
http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo
Hopefully, we'll have something for 8.2

Oleg
On Tue, 6 Sep 2005, Doug Bloebaum wrote:

On 9/6/05, Andrey Fomichev <fomichev@ispras.ru> wrote:

- Are there any of you who need to store and query XML data?

- Do you already use PostgreSQL to store XML data or you are

just thinking about it?

The project I'm currently working on uses XML for both data

extraction from

Postgres and as a means to transmit data to Postgres for storage.

- If you store XML data in PostgreSQL, how do you do it? What tool

do you use?

We're using Oracle's XSQL servlet (

http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b1079

4/adx09xsq.htm)

with Apache tomcat (http://jakarta.apache.org/tomcat/) as its servlet
container. The only change we've made to XSQL is the addition of a custom
tag (<xsql:pg-func>) in order to make use of Postgres functions returning
REF CURSOR. Aside from that, we're using XSQL out-of-the-box.

Granted, we're not really manipulating XML within the database, rather

we're

using XML as a sort of database-neutral interface.

- Do you need some advanced query capabilities? Like XQuery, XPath 2.0

No.

- Do you need some advanced update capabilities? Like node-level updates

No.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

On Tue, 06 Sep 2005 18:48:01 +0400, Andrey Fomichev wrote:

Hello!

I would like to raise a discussion about XML support in PostgreSQL. I'm a
person who is quite experienced in XML data management and new to
PostgreSQL. So, excuse me if I ask trivial questions about PostgreSQL...

I tried to find something about support for XML in PostrgreSQL. As far as
I understand, PosgreSQL does not have native support for XML. But I've
found several works around.
1. XML databases build on top of PostgreSQL. They are XpSQL and XDB
(sorry,
if I missed something). Are they alive and functionable?
2. Contribution made by John Gray (xml and xml2). This is a tool that uses
'shredding' for storing XML in relational tables (another words, it
decomposes XML document into nodes and places these nodes into tables
in such a way that we can reconstruct this document back). What do you
think about this tool?

Actually, it doesn't do anything to the document - it just treats it as a
text string (I believe XpSQL, for example, decomposes a document into
constituent parts). The xml2 contrib module is concerned with providing
convenient functions to use XPath queries against such documents. I'd love
to do better indexing of them, and have looked at GiST, but I haven't got
the understanding needed to accomplish that!

In general, I have the following questions to PostgreSQL users. - Are
there any of you who need to store and query XML data? - Do you already
use PostgreSQL to store XML data or you are
just thinking about it?
- If you store XML data in PostgreSQL, how do you do it? What tool
do you use?
- Do you need some advanced query capabilities? Like XQuery, XPath 2.0 -
Do you need some advanced update capabilities? Like node-level updates

And I have the following question to PostgreSQL developers. Do you have
any plans for native support for XML in PostgreSQL? By native support I
understand persistent data structured specially developed for storing XML
on disk and query/update it efficiently. As far as I know, native
support for XML is a way other database vendors (Oracle, Microsoft, IBM)
go.

I think there's quite a bit of "buzzword" usage going on from database
vendors - it depends on what your use case is what sort of approach you
want to take. The advantage of decomposing documents is that you can more
readily access the structure of them, but recomposing them is slower.
Storing documents as text strings has the advantage that they are easily
retrieved (and verified against digital signatures, which may be important
for some applications) but are more difficult to represent as a DOM
(though how that fits with a relational model at all is the question!)

Updates would be interesting - but quite a large project, I suspect.

I hope that helps. I am a developer more than a user and I can't speak for
people's usage. Others may well do that.

Regards

John

In general, I have the following questions to PostgreSQL users. - Are
there any of you who need to store and query XML data? - Do you already
use PostgreSQL to store XML data or you are
just thinking about it?
- If you store XML data in PostgreSQL, how do you do it? What tool
do you use?
- Do you need some advanced query capabilities? Like XQuery, XPath 2.0 -
Do you need some advanced update capabilities? Like node-level updates

We have a software core that is used across 50+ web sites. Each web site
can define their own object. Each object has an XML definition, which
allows for flexibility without need to change the database. Currently we
break the objects into elements and pour them into a table called
elements. There is a driver that breaks and recreates the objects. The
application is written in php and the elements table has between 3 and 4
million records (the whole database incl. indexes is about 2 GB) at the
moment and the performance is just fine.

I do not know if this is close to what you are asking, just thought it
might be useful.

Thank you very much for your answers.

Friendly,
Andrey