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
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.