undefined | Better HN

0 pointsfizx16y ago0 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.

0 comments

3 comments · 1 top-level

sethg16y ago· 2 in thread

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")

Documentation here: http://www.postgresql.org/docs/8.3/interactive/functions-xml...

nradov16y ago

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.

sethg16y ago

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.

j / k navigate · click thread line to collapse