Обсуждение: Current state of XML capabilities in PostgreSQL?

Hello,

I'm looking for the current state of XML capabilities in PostgreSQL and I'm coming up with a lot of confusing links and a bit short on documentation.

I first read that there used to be an xml2 contrib module for Postgres that provided a lot of functions to read/write and simultaneously validating XML, and searching and returning columns with XML based upon XPath expressions, etc...  But I read that the capability was going to be moved into the Postgres baseline (this was 8.3-ish?).  Currently, all I've ended up finding is the lone official document page on the XML data type (http://www.postgresql.org/docs/current/static/datatype-xml.html), XML functions and operators (http://www.postgresql.org/docs/current/interactive/functions-xml.html), and the XML Support Pg wiki page (http://wiki.postgresql.org/wiki/XML_Support).

I just plan on storing already-made XML in a column, then be able to search on it using where clauses with XPath expressions (e.g. select tv_show,xpath('/station/times', xml_data) from table_with_xml where xpath('/name', xml_data)='Captain Kangaroo').  I do understand there is an xpath() function to return data, so that's solved for.

I'm looking for the functionality that I mentioned above that allowed more comprehensive XML operations but I'm just not finding any documentation for it, if it exists.

I have 8.4.0 now, but can go to .1 if there have been great strides made since .0 was released.
If anyone can help me out, I would be greatly appreciated.

Thanks,
Nate

Nathan Widmyer wrote:
> I just plan on storing already-made XML in a column, then be able to
> search on it using where clauses with XPath expressions (e.g. select
> tv_show,xpath('/station/times', xml_data) from table_with_xml where
> xpath('/name', xml_data)='Captain Kangaroo').  I do understand there
> is an xpath() function to return data, so that's solved for.

why would you store data thats wrapped in two copies of its fieldname
along with other punctuation?    wouldn't it make more sense to
decompose your XML source into proper tables so proper indexes and
relational sql queries can be made?     otherwise, every query turns
into a massive sequential scan and parsing operation.

On Wed, Sep 16, 2009 at 5:24 PM, John R Pierce <pierce@hogranch.com> wrote:
> why would you store data thats wrapped in two copies of its fieldname along
> with other punctuation?    wouldn't it make more sense to decompose your XML
> source into proper tables so proper indexes and relational sql queries can
> be made?     otherwise, every query turns into a massive sequential scan and
> parsing operation.

you can always have index on xpath() ...
and than use same expression in WHERE, and postgresql will use index.


--
GJ

> I'm looking for the current state of XML capabilities in PostgreSQL and
> I'm coming up with a lot of confusing links and a bit short on
> documentation.


Postgres' XML is still lacking in a few spots. But the core
functionality is certainly there to do all of the every day stuff.

The trick with working with XML in the database is to know when to store
it as XML and when to shred it into relational tables. If its a
structured document (looks like something you'd open in Word) save it as
XML. If it looks like something you'd open in Excel then start
shredding. And it looks like maybe you want to do a tv guide type thing.
   That is something you'll want to shred to tables.

> I'm looking for the functionality that I mentioned above that allowed
> more comprehensive XML operations but I'm just not finding any
> documentation for it, if it exists.

What exactly is it you are trying to do but cant?

Scott

I have this XML that I don't wish to parse out to tables (BTDT and
don't wish to go there  for the most part).  There might be the
uncommon chance where an XML-friendly operation would be very useful.

On 9/16/09, Scott Bailey <artacus@comcast.net> wrote:
>> I'm looking for the current state of XML capabilities in PostgreSQL and
>> I'm coming up with a lot of confusing links and a bit short on
>> documentation.
>
>
> Postgres' XML is still lacking in a few spots. But the core
> functionality is certainly there to do all of the every day stuff.
>
> The trick with working with XML in the database is to know when to store
> it as XML and when to shred it into relational tables. If its a
> structured document (looks like something you'd open in Word) save it as
> XML. If it looks like something you'd open in Excel then start
> shredding. And it looks like maybe you want to do a tv guide type thing.
>    That is something you'll want to shred to tables.
>
>> I'm looking for the functionality that I mentioned above that allowed
>> more comprehensive XML operations but I'm just not finding any
>> documentation for it, if it exists.
>
> What exactly is it you are trying to do but cant?
>
> Scott
>
>

Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:

>> why would you store data thats wrapped in two copies of its fieldname along
>> with other punctuation?    wouldn't it make more sense to decompose your XML
>> source into proper tables so proper indexes and relational sql queries can
>> be made?     otherwise, every query turns into a massive sequential scan and
>> parsing operation.

> you can always have index on xpath() ...
> and than use same expression in WHERE, and postgresql will use index.

Interesting. I had thought that it was not possible to index
on XML columns because no comparison operators were defined:

| tim=# CREATE TEMPORARY TABLE tmpXML (x XML PRIMARY KEY);
| FEHLER:  Datentyp xml hat keine Standardoperatorklasse für Zugriffsmethode »btree«
| HINT:  Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp
definieren.

yet an array of XML works:

| tim=# CREATE TEMPORARY TABLE tmpXML (x XML[] PRIMARY KEY);
| HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »tmpxml_pkey« für Tabelle »tmpxml«
| CREATE TABLE
| tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '<?xml
version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')));
| INSERT 0 1

though only once:

| tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '<?xml
version="1.0"?><book><title>Manual2</title><chapter>...</chapter></book>'))); 
| FEHLER:  konnte keine Vergleichsfunktion für Typ xml ermitteln

So an index on xpath() should not be possible.

Tim