I'm using PostgreSQL 8.4 XPath (XML functions) feature. This is an adapted example found in docs:
SELECT xpath('/my:a/value[.>15]',
'<my:a xmlns:my="http://example.com">
<value>20</value>
<value>10</value>
<value>30</value>
</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
This works fine, it returns a list of nodes correctly filtered with "value>15" condition:
xpath
xml[]
---------------------------------------
"{<value>20</value>,<value>30</value>}"
But when I try to use "sum" it returns an empty list instead of a scalar value:
SELECT xpath('sum(/my:a/value[.>15])',
...
result:
xpath
xml[]
-----
"{}"
Any suggestions?
Juan Ferreyra
xpath does not create new nodes, it only filters the currently existing ones. That's what XSLT is for.
From the docs:
The function xpath evaluates the XPath expression xpath against the XML value xml. It returns an array of XML values corresponding to the node set produced by the XPath expression.
Since your XPath expression does not return a node set, there are not values in it and there is nothing to return.
I stumbled across the same problem and I am pleased to add another answer to the question:
With PostgreSQL 9.2 the documentation suddenly has one more sentence covering the xpath function:
If the XPath expression returns a scalar value rather than a node set,
a single-element array is returned.
Just what I need! So in relation to the question another valid answer is: Upgrade to PostgreSQL 9.2. In the moment of writing this, version 9.2 is just a beta, but I can confirm that this works:
Version details
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)
Demonstration of solution
(This demonstration was made with Postgre 9.5, because originally I pasted the wrong code)
postgres=# SELECT xpath('sum(/my:a/value[.>15])', '<my:a xmlns:my="http://example.com">
postgres'# <value>20</value>
postgres'# <value>10</value>
postgres'# <value>30</value>
postgres'# </my:a>',
postgres(# ARRAY[ARRAY['my', 'http://example.com']]);
xpath
-------
{50}
(1 row)
