Thread: XPath PostgreSQL 8.4

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

Karl Koster <klkoster@optonline.net> wrote:

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

You have to put brackets around the function call:

| select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id from risk.trade_table;

Tim

I wrote:

> [...]
> You have to put brackets around the function call:

> | select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id from risk.trade_table;

... or, after a look in the dictionary, whatever you call
"(" and ")" :-).

Tim