Обсуждение: PostgreSQL's XML support comparison against other RDBMSes

Hello,

Does anybody know good comparison/review article of XML support in
major RDMBSes? I know that PostgreSQL's XML capabilities are quite
weak, but how far is it from other products?

Sincerely,
Ivan Zolotukhin

On Mon, Mar 27, 2006 at 05:22:40PM +0400, Ivan Zolotukhin wrote:
> Hello,
>
> Does anybody know good comparison/review article of XML support in
> major RDMBSes? I know that PostgreSQL's XML capabilities are quite
> weak, but how far is it from other products?

For other databases it's probably somewhere between PostgreSQL and
Oracle. :)

BTW, something to consider is that thanks to PostgreSQL's ability to use
so many different languages for stored procedures, there's a lot you can
do in XML in the database via stored procs. I believe perl has pretty
good XML support, for example. And of course there's java...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Mon, 27 Mar 2006 07:31:36 -0600, Jim C. Nasby wrote:

> On Mon, Mar 27, 2006 at 05:22:40PM +0400, Ivan Zolotukhin wrote:
>> Hello,
>>
>> Does anybody know good comparison/review article of XML support in major
>> RDMBSes? I know that PostgreSQL's XML capabilities are quite weak, but
>> how far is it from other products?
>
> For other databases it's probably somewhere between PostgreSQL and Oracle.
> :)

DB2's upcoming Viper storage engine will change that drastically. It runs
in parallel with the traditional relational engine but has provisions for
correctly updating the XML in ways that are suitable for trees.

> BTW, something to consider is that thanks to PostgreSQL's ability to use
> so many different languages for stored procedures, there's a lot you can
> do in XML in the database via stored procs. I believe perl has pretty good
> XML support, for example. And of course there's java...

Regardless of language you are still limited to tables as storage
structure. XML does not fit well into tables, even though many DBAs (as
well as programmers and managers) seem to have a problem grasping that.

AFAIK MySQL wanted to use BerkeleyDB/XML for XML support. Not sure what has
become of those plans in light of the Sleepycat buyout by Oracle and the
supposedly planned Firebird engine adoption. PostgreSQL could really set
itself apart by adopting a similar mechanism, including XQuery/XUpdate
support.
Unfortunately the engineering efforts for such a solution are significant;
my guess is that IBM will have a huge advantage over everyone else in this
area.

Holger

> Does anybody know good comparison/review article of XML support in
> major RDMBSes? I know that PostgreSQL's XML capabilities are quite
> weak, but how far is it from other products?

Could you please desribe a specific example of what you would like to
do and how you would not be able to do that via PostgreSQL.  I think
you will find with functions of various languages you can do just
about anything.  You could for example load up exported functions from
Xalan/Xerces XML libaray.

Merlin

Hello,

On 3/28/06, Merlin Moncure <mmoncure@gmail.com> wrote:
> > Does anybody know good comparison/review article of XML support in
> > major RDMBSes? I know that PostgreSQL's XML capabilities are quite
> > weak, but how far is it from other products?
>
> Could you please desribe a specific example of what you would like to
> do and how you would not be able to do that via PostgreSQL.  I think
> you will find with functions of various languages you can do just
> about anything.  You could for example load up exported functions from
> Xalan/Xerces XML libaray.

Actually, at this moment I'm just interested in possibilities to
combine relational and XML approaches in my projects and therefore
want to understand defferences between implementations of XML support
in DBMSes. I'm sure that I won't stop using relational model - so,
native XML databases are almost out of my interest.

For example, let's consider web application with heterogeneous data
(internal data is stored in relational tables but there is a lot of
data from outside, that were imported from different places and -
surely, as XML is the language to speak with other apps - it's much
better to store this data 'as is', e.g. as a set of XMLs. Then, I need
to manipulate with mixture of relational and XML portions of data...)

Sure, PostgreSQL is the main DB I use. So, at the best I'll use it's
abilities, but I see that its XML support is weak.

BTW, are there any plans for its improvement? What are the first steps
for the people who want to contribute to PostgreSQL in this field?

Sincerely,
Ivan Zolotukhin

Ivan Zolotukhin wrote:
> BTW, are there any plans for its improvement? What are the first
> steps for the people who want to contribute to PostgreSQL in this
> field?

The first step would be making a detailed analysis of what "XML support"
is supposed to mean.  (You alluded to one use case, but before we
commit to anything, we need to see a more general analysis.)  The
second step would be checking how some of these issues can be solved
within existing standards.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 3/29/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> Ivan Zolotukhin wrote:
> > BTW, are there any plans for its improvement? What are the first
> > steps for the people who want to contribute to PostgreSQL in this
> > field?
>
> The first step would be making a detailed analysis of what "XML support"
> is supposed to mean.

So let's make such analysis here within PostgreSQL community! There
were a lot of talks about XML support in Postgres, but they did not
lead to any steady work in this direction. IMHO, it's time to start it
now, no?

Sincerely,
Ivan Zolotukhin

On Wed, Mar 29, 2006 at 02:57:44PM +0400, Ivan Zolotukhin wrote:
> So let's make such analysis here within PostgreSQL community! There
> were a lot of talks about XML support in Postgres, but they did not
> lead to any steady work in this direction. IMHO, it's time to start it
> now, no?

Quite, lets start with why the module in contrib is not good enough and
what the various existing XML projects lack.

http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/xml2/
http://pgfoundry.org/projects/getxml/
http://gborg.postgresql.org/project/xpsql/projdisplay.php

As for myself, I've never had XML even come close to my database so I
have no real interest in this.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

The getxml and xpsql project seem to fit phantasticaly into my idea of handling XML inside a database.
Say you wan't to store an OpenOffice.org document into a database, but rather than say "it's a binary" and lose all editing and most search capabilities, it might be possible to use xpsql to store the document into the database. Once stored, specific parts could be altered as the system changes states and during the systems lifecycle, you could at any time generate a document based on the current system state.
Can anyone give me any kind of opinion on this kind of document handling?
Does anyone here have any experiance with xpsql?
Is xpsql under any kind of active development?
Can xpsql handle documents as complex as OOo documents, with all their DTDs, schemas...whatever?

t.n.a.

On 3/29/06, Ivan Zolotukhin <ivan.zolotukhin@gmail.com> wrote:
> On 3/29/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> > Ivan Zolotukhin wrote:
> > > BTW, are there any plans for its improvement? What are the first
> > > steps for the people who want to contribute to PostgreSQL in this
> > > field?
> >
> > The first step would be making a detailed analysis of what "XML support"
> > is supposed to mean.
>
> So let's make such analysis here within PostgreSQL community! There
> were a lot of talks about XML support in Postgres, but they did not
> lead to any steady work in this direction. IMHO, it's time to start it
> now, no?

IMO, PostgreSQL has the tools to do amazing things in XML. Check out:
1. pl languages (especially pl/perl)
2. funtional indexes
3. xml contrib
4. gist indexes

I agree wtih martijn.  If I have XML requirement, I either store it as
black box blob or preprocesss it (possibly with XSLT) into insert
statements.  I treat XML no differently than a  CSV file for example.

Merlin

On Mar 29, 2006, at 2:57 AM, Ivan Zolotukhin wrote:

> On 3/29/06, Peter Eisentraut <peter_e@gmx.net> wrote:
>> Ivan Zolotukhin wrote:
>>> BTW, are there any plans for its improvement? What are the first
>>> steps for the people who want to contribute to PostgreSQL in this
>>> field?
>>
>> The first step would be making a detailed analysis of what "XML
>> support"
>> is supposed to mean.
>
> So let's make such analysis here within PostgreSQL community! There
> were a lot of talks about XML support in Postgres, but they did not
> lead to any steady work in this direction. IMHO, it's time to start it
> now, no?

The people who want it should explain what it is, ideally by
pointing at the relevant standards documents.

Cheers,
   Steve

May I ask a question about this?

I will be working on an older database in which the original developer
stored XML documents as a single variable length text field.  To process it,
it has to be retrieved in full and parsed.  But the structure of it is
simple in that it has an element for each field in the DB that replaced the
earily version.  But people are still using the earlier one because they
still need access to the old data and no tool has yet been written by my
predecessors to move the old data over to the new DB.  Does the XML support
you're all talking about make it less tedious to develop tools to parse
these old XML files and put their data into the right field of the right
table?  I can develop the tool our users need using the resources I have at
hand, but the proces of parsing these XML files is certain to be very
tedious; something I am not looking forward to.  There is a reason I don't
do much with XML even though I know how.

Ted
----- Original Message -----
From: "Steve Atkins" <steve@blighty.com>
To: "pgsql general" <pgsql-general@postgresql.org>
Sent: Wednesday, March 29, 2006 12:21 PM
Subject: Re: [GENERAL] PostgreSQL's XML support comparison against other
RDBMSes


>
> On Mar 29, 2006, at 2:57 AM, Ivan Zolotukhin wrote:
>
>> On 3/29/06, Peter Eisentraut <peter_e@gmx.net> wrote:
>>> Ivan Zolotukhin wrote:
>>>> BTW, are there any plans for its improvement? What are the first
>>>> steps for the people who want to contribute to PostgreSQL in this
>>>> field?
>>>
>>> The first step would be making a detailed analysis of what "XML
>>> support"
>>> is supposed to mean.
>>
>> So let's make such analysis here within PostgreSQL community! There
>> were a lot of talks about XML support in Postgres, but they did not
>> lead to any steady work in this direction. IMHO, it's time to start it
>> now, no?
>
> The people who want it should explain what it is, ideally by
> pointing at the relevant standards documents.
>
> Cheers,
>   Steve
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

On 3/29/06, Ted Byers <r.ted.byers@rogers.com> wrote:
> May I ask a question about this?
>
> I will be working on an older database in which the original developer
> stored XML documents as a single variable length text field.  To process it,
> it has to be retrieved in full and parsed.  But the structure of it is

heheh :)

> simple in that it has an element for each field in the DB that replaced the
> earily version.  But people are still using the earlier one because they
> still need access to the old data and no tool has yet been written by my
> predecessors to move the old data over to the new DB.  Does the XML support
> you're all talking about make it less tedious to develop tools to parse
> these old XML files and put their data into the right field of the right
> table?  I can develop the tool our users need using the resources I have at
> hand, but the proces of parsing these XML files is certain to be very
> tedious; something I am not looking forward to.  There is a reason I don't
> do much with XML even though I know how.

Most high level languages these days have decent xml parsing power.
My suggestion would be to parse it into insert statements the easiest
way possible.  Following that just stuff it into the new database.
PostgreSQL string processing is powerful enough that you can create a
view which presents the old structure if you want your legacy app to
continue to access your database without substantion modication.
Check out array_cat, array_to_string, etc.  You could explore
developing custom aggregates if necessary

Merlin

Ted Byers schrieb:
> May I ask a question about this?
>
> I will be working on an older database in which the original developer
> stored XML documents as a single variable length text field.  To process
> it, it has to be retrieved in full and parsed.  But the structure of it
> is simple in that it has an element for each field in the DB that
> replaced the earily version.  But people are still using the earlier one
> because they still need access to the old data and no tool has yet been
> written by my predecessors to move the old data over to the new DB.
> Does the XML support you're all talking about make it less tedious to
> develop tools to parse these old XML files and put their data into the
> right field of the right table?  I can develop the tool our users need
> using the resources I have at hand, but the proces of parsing these XML
> files is certain to be very tedious; something I am not looking forward
> to.  There is a reason I don't do much with XML even though I know how.
>
Hm. well if my mind does not fool me you wrote the exact posting
sometime ago ;)

and I also remember answers in the region of parsing it with a stored
function and for your legacy application put a view.

Otoh, if you want it "smarter" and "nicer" you could write a whole
datatype with default input/output is your plain XML text.

However - since XML is very generic you cannot expect some standards
implementation to cover all _your_ needs. I would just use a dom
or sax parser in my favourite scripting language and distribute
the items over some tables as dom nodes and out the whole logic
into a custom datatype.

Regards
Tino

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Wed, Mar 29, 2006 at 02:57:44PM +0400, Ivan Zolotukhin wrote:
> > So let's make such analysis here within PostgreSQL community! There
> > were a lot of talks about XML support in Postgres, but they did not
> > lead to any steady work in this direction. IMHO, it's time to start it
> > now, no?
>
> Quite, lets start with why the module in contrib is not good enough and
> what the various existing XML projects lack.
>
> http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/xml2/
> http://pgfoundry.org/projects/getxml/
> http://gborg.postgresql.org/project/xpsql/projdisplay.php
>
> As for myself, I've never had XML even come close to my database so I
> have no real interest in this.

As I remember the various XML capabilities asked for in the past were:

    query via XML
    results via XML
    retrive/update parts of the XML document

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Maybe add to it the
          insert/update data in tables based on an XML (a'la MS
updategram)

Balázs