Thread: 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.
Attachment
On 3/29/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
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
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