It looks like I have to abandon xml2 functions in PostgreSQL 8.4. The
problem is I can't seem to find an incantation of xpath that will
perform the same thing. I have tried the following snippet:
select xpath('/trade/trade-info/id/text()', cast(xml as xml))[1] as id
from risk.trade_table
which, from the documentation should give me the first (and only in this
case) xml node text value for the XPath expression. Instead I get the
following error message from the SQL parser:
ERROR: syntax error at or near "["
LINE 1: ...h('/trade/trade-info/id/text()', cast(xml as xml))[1] as id ...
^
********** Error **********
ERROR: syntax error at or near "["
SQL state: 42601
Character: 62
When I run the select statement without an array index, it correctly
returns a single column of arrays of length one (expected) for the XPath
node text value. Can anyone shine a light on what I am doing wrong?
The build of the 8.4 database I am using is 8.4.1.9250.
Thanks,
Karl