Re: [GENERAL] Current state of XML capabilities in PostgreSQL?
Grzegorz Jaśkiewicz 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 'Manual...'))); | INSERT 0 1 though only once: | tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT 'Manual 2...'))); | FEHLER: konnte keine Vergleichsfunktion für Typ xml ermitteln So an index on xpath() should not be possible. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Current state of XML capabilities in PostgreSQL?
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 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 > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Current state of XML capabilities in PostgreSQL?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Current state of XML capabilities in PostgreSQL?
On Wed, Sep 16, 2009 at 5:24 PM, John R Pierce 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Current state of XML capabilities in PostgreSQL?
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
