PostgreSQL 8.3 (maybe earlier versions too, I haven't checked) has an xml data type. I haven't used it personally, but if I read the documentation correctly, you can do something like this:
SELECT id, xpath('//node[@type=''title'']', doc) AS title,
xpath('/@version', doc) AS version
FROM documents WHERE version=2;
(assuming your "documents" table has a "doc" field of type "xml")
The trouble is that PostgreSQL still doesn't support indexing into XML fields based on XPath statements. So if you need to look for some particular XML element value in the WHERE clause then it's going to be really slow.
According to the documentation, you can't index on XML directly because there are no XML comparison operators (I suspect this is to avoid hair-pulling debates over whether `<foo x='y'>` should compare equal to `<foo x="y"></foo>`). But you can serialize the XML, or a fragment of XML retrieved by XPath, and cast it to text, and you can build an index on the result of that serialization. (PostgreSQL has supported indexing on expressions for a loong time.)
I think the place you lose is if you have some arbitrary XPath expression for which you haven't constructed a DB index yet, and you want to search on that expression.
Comments
I have a table that stores xml documents. The document schema has evolved over time, and is a representation of a print-on-demand(able) brochure.
In my documents table, I have a field called "title". I have an after save hook in my application code to update this field.
If I had an XML-aware database, I could:
"SELECT id, //node[@type='title'] AS title, /@version AS version FROM documents WHERE version=2;"
This is more elegant than creating extra columns and hooks to support future queries, and mutilating your table with tens of extra columns.
PostgreSQL 8.3 (maybe earlier versions too, I haven't checked) has an xml data type. I haven't used it personally, but if I read the documentation correctly, you can do something like this:
(assuming your "documents" table has a "doc" field of type "xml")Documentation here: http://www.postgresql.org/docs/8.3/interactive/functions-xml...
The trouble is that PostgreSQL still doesn't support indexing into XML fields based on XPath statements. So if you need to look for some particular XML element value in the WHERE clause then it's going to be really slow.
According to the documentation, you can't index on XML directly because there are no XML comparison operators (I suspect this is to avoid hair-pulling debates over whether `<foo x='y'>` should compare equal to `<foo x="y"></foo>`). But you can serialize the XML, or a fragment of XML retrieved by XPath, and cast it to text, and you can build an index on the result of that serialization. (PostgreSQL has supported indexing on expressions for a loong time.)
I think the place you lose is if you have some arbitrary XPath expression for which you haven't constructed a DB index yet, and you want to search on that expression.