Thread: XML in PostgreSQL
Hi, Surprisingly there doesn't seem to be much discussion about the issue of storing and working with XML in PostgreSQL. Or maybe this is the wrong list to look for that ? The README in Contrib/XML in PostgreSQL 7.2.2 by John Gray is dated 16 August 2001, and the identically dated TODO file lists some very important things. Has there been any update of this or related work for PostgreSQL ? My interest is in working with 'document-centric' XML. Would using an external XML module, such as Python's PyXML allow for sufficiently quick performance, or at least performance that would be comparable to that of using a native PostgreSQL module ? Any and all suggestions, opinions welcome Cheers, Joel
Are you trying to get some schema in Postgres database design to match some XML schema, or just store XML in Postgres? Joel Rodrigues wrote: > > Hi, > > Surprisingly there doesn't seem to be much discussion about the > issue of storing and working with XML in PostgreSQL. Or maybe > this is the wrong list to look for that ? > > The README in Contrib/XML in PostgreSQL 7.2.2 by John Gray is > dated 16 August 2001, and the identically dated TODO file lists > some very important things. Has there been any update of this or > related work for PostgreSQL ? > > My interest is in working with 'document-centric' XML. Would > using an external XML module, such as Python's PyXML allow for > sufficiently quick performance, or at least performance that > would be comparable to that of using a native PostgreSQL module ? > > Any and all suggestions, opinions welcome > > Cheers, > Joel > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Carpe Dancem ;-) ----------------------------------------------------------------- Remember your friends while they are alive ----------------------------------------------------------------- Sincerely, Dennis Gearon
Joel Rodrigues writes: > Surprisingly there doesn't seem to be much discussion about the > issue of storing and working with XML in PostgreSQL. Or maybe > this is the wrong list to look for that ? The main reason nothing is happening is that no one can really come up with the grand master design plan for this. There's an SQL standard under development which addresses various ways to combine SQL and XML. Once that is finished we have something to work with. Incidentally, that standard depends on XQuery getting done first, so don't hold your breath. -- Peter Eisentraut peter_e@gmx.net
On Thursday, February 27, 2003, at 12:11 , Dennis Gearon wrote: > Are you trying to get some schema in Postgres database design to match > some XML schema, or just store XML in Postgres? Well, to store entire XML documents. So-called 'document-centric' XML. Things like articles, interviews, press releases, photo essays, etc. And then be able to do X-Path (or X-Path-like) queries on them. I'm an absolute beginner on this issue. I was looking for a solution to the implementation of a document publishing system using Python & PostgreSQL, and this appears to be the right direction. Some of my reading for this : Reading XML and Databases by Ronald Bourret : Storing Documents in BLOBs http://www.rpbourret.com/xml/XMLAndDatabases.htm#blob Persistence: SGML and XML in Databases http://www.isgmlug.org/database.html XML data management systems By Paul Sholtz http://www.newarchitectmag.com/archives/2002/10/ > On Thursday, February 27, 2003, at 07:19 , Peter Eisentraut wrote: > > The main reason nothing is happening is that no one can really come up > with the grand master design plan for this. There's an SQL > standard under > development which addresses various ways to combine SQL and XML. Once > that is finished we have something to work with. Incidentally, that > standard depends on XQuery getting done first, so don't hold > your breath. The seemingly ceaseless stream of 'Working Draft' and 'Candidate Recommendation' announcements from the W3C leaves me on the verge of a hissy fit. Not to mentioned very confused. Leaving aside what might come to be in the future, the reality is that ways & means of dealing with XML right now in PostgreSQL *are* needed and Gray's contrib/xml seems like a good start. I imagine that a workaround is possible in PostgreSQL using the idea mentioned in the articles cited above, of storing the XML document in it's entirety in one table and using another table as an 'index table'. Cheer, Joel
On Thu, 27 Feb 2003 05:28:58 +0000, Joel Rodrigues wrote: > Hi, > > Surprisingly there doesn't seem to be much discussion about the issue of > storing and working with XML in PostgreSQL. Or maybe this is the wrong > list to look for that ? > > The README in Contrib/XML in PostgreSQL 7.2.2 by John Gray is dated 16 > August 2001, and the identically dated TODO file lists some very > important things. Has there been any update of this or related work for > PostgreSQL ? > This list is probably as good as any! I did some client-specific development work on contrib/xml last September - which also had the effect of verifying that it was faster than I expected (and libxml2 supports quite a rich part of the XPath syntax, so it's quite a handy thing). In terms of the TODO items, my feeling is: 1) Persistent storage: complicated and unnecessary for only a small benefit. 2) XQuery: don't know 3) DOM interfaces: Runs into the persistent storage problem. On-the-fly parsing seems OK for document fragments. 4)Returning sets of documents: the new set-returning functions make this sort of thing much more straightforward. Indexing - still an interesting possibility I think but haven't really thought further about it. [There is someone who has contacted me because he's adding a Set-returning function to contrib/xml. To my shame I haven't replied to one of his questions yet. 5) Return types - this is a straightforward case of writing wrapper/conversion functions. This was one of the things I did for my client in September. > My interest is in working with 'document-centric' XML. Would using an > external XML module, such as Python's PyXML allow for sufficiently quick > performance, or at least performance that would be comparable to that of > using a native PostgreSQL module ? > This was my environment too (I'm now in full-time employment as a broadcast engineer who doesn't have much work opportunity to use Pg however) and I'd have liked to have done more work with the module. There are people using it and it seems to work OK. What sort of things do you want to do? Regards John
Hi, I'm sending this again since it hasn't shown up on the list (yet !). - Joel On Thursday, February 27, 2003, at 12:11 , Dennis Gearon wrote: > Are you trying to get some schema in Postgres database design to match > some XML schema, or just store XML in Postgres? Well, to store entire XML documents. So-called 'document-centric' XML. Things like articles, interviews, press releases, photo essays, etc. And then be able to do X-Path (or X-Path-like) queries on them. I'm an absolute beginner on this issue. I was looking for a solution to the implementation of a document publishing system using Python & PostgreSQL, and this appears to be the right direction. Some of my reading for this : Reading XML and Databases by Ronald Bourret : Storing Documents in BLOBs http://www.rpbourret.com/xml/XMLAndDatabases.htm#blob Persistence: SGML and XML in Databases http://www.isgmlug.org/database.html XML data management systems By Paul Sholtz http://www.newarchitectmag.com/archives/2002/10/ > On Thursday, February 27, 2003, at 07:19 , Peter Eisentraut wrote: > > The main reason nothing is happening is that no one can really come up > with the grand master design plan for this. There's an SQL > standard under > development which addresses various ways to combine SQL and XML. Once > that is finished we have something to work with. Incidentally, that > standard depends on XQuery getting done first, so don't hold > your breath. The seemingly ceaseless stream of 'Working Draft' and 'Candidate Recommendation' announcements from the W3C leaves me on the verge of a hissy fit. Not to mentioned very confused. Leaving aside what might come to be in the future, the reality is that ways & means of dealing with XML right now in PostgreSQL *are* needed and Gray's contrib/xml seems like a good start. I imagine that a workaround is possible in PostgreSQL using the idea mentioned in the articles cited above, of storing the XML document in it's entirety in one table and using another table as an 'index table'. Cheer, Joel
* Joel Rodrigues <borgempath@Phreaker.net> [2003-03-04 06:58]: > Reading XML and Databases by Ronald Bourret : Storing Documents in BLOBs > http://www.rpbourret.com/xml/XMLAndDatabases.htm#blob > > Persistence: SGML and XML in Databases > http://www.isgmlug.org/database.html > > XML data management systems By Paul Sholtz > http://www.newarchitectmag.com/archives/2002/10/ Hmm... I'd better get to Amazon, thanks. > >On Thursday, February 27, 2003, at 07:19 , Peter Eisentraut wrote: > > > >The main reason nothing is happening is that no one can really come up > >with the grand master design plan for this. There's an SQL > >standard under development which addresses various ways to > >combine SQL and XML. Once that is finished we have something to > >work with. Incidentally, that standard depends on XQuery getting > >done first, so don't hold your breath. > > The seemingly ceaseless stream of 'Working Draft' and 'Candidate > Recommendation' announcements from the W3C leaves me on the > verge of a hissy fit. Not to mentioned very confused. > > Leaving aside what might come to be in the future, the reality > is that ways & means of dealing with XML right now in PostgreSQL > *are* needed and Gray's contrib/xml seems like a good start. > > I imagine that a workaround is possible in PostgreSQL using the > idea mentioned in the articles cited above, of storing the XML > document in it's entirety in one table and using another table > as an 'index table'. Why store the whole document in PostgreSQL? Why not leave it on the file system where you can have at it with xsltproc or xalan? Incidently, I am building a document management system in PostgreSQL that manages XML documents. This subject is very interesting to me. I've found that mapping XML data to an RDBMS is difficult and kludgy. It was a real breakthough for me when I realized that not every document had to be ER modeled. -- Alan Gutierrez - ajglist@izzy.net http://khtml-win32.sourceforge.net/ - KHTML on Windows
borgempath@Phreaker.net (Joel Rodrigues) wrote in message news:<0C9D3330-4A14-11D7-A8D5-0005024EF27F@Phreaker.net>... > Hi, > > Surprisingly there doesn't seem to be much discussion about the > issue of storing and working with XML in PostgreSQL. Or maybe > this is the wrong list to look for that ? > > The README in Contrib/XML in PostgreSQL 7.2.2 by John Gray is > dated 16 August 2001, and the identically dated TODO file lists > some very important things. Has there been any update of this or > related work for PostgreSQL ? > > My interest is in working with 'document-centric' XML. Would > using an external XML module, such as Python's PyXML allow for > sufficiently quick performance, or at least performance that > would be comparable to that of using a native PostgreSQL module ? > > Any and all suggestions, opinions welcome > > Cheers, > Joel > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html You can always do what I did. I created functions to handle specific tasks, such as retrieving product information and assembled the XML via the function off the fields that I was interested in. When the information was compiled, ptewwwy, I had the function spit the XML out and pass it back to the caller then told ASP to disassemble the XML string via XSLT and there you have it. Yes it might be kludgey, but it beats waiting. And since XML is XML as long as you stuck to <root><catalogue><product>Shoe<description>a black shoe</description></product><product>Box<description>it's a box</description></product></catalogue></root> You're okay. HTH
Hi, ConnectTel has a product that allows access to PostgreSQL using XML/XSL. It allows query and updates. Please, go to this page and check it out. http://www.connecttel.com/index.php?link=_XQX Rosimildo.