i am building a small module for a Project for queriing xml-data stored in a postgres Database (column-type is "xml").
it is relatively simple to do simple queries on xml like:
<root> <name>Alice</name> <refNr>15</refNr> </root> select id from table where xpath('/root/name/text()')[1]::text = 'Alice';
or
select id from table where xpath('/root/refNr/text()')[1]::text::int BETWEEN 10 AND 20;
What i am struggling with is, when an element occurs more than once, like
<root> <name>Alice</name> <refNr>25</refNr> <refNr>15</refNr> </root>
if i now place the second query, obviously, as i explicitly state i want element[1]=25
25 BETWEEN 10 AND 20
is false, so i won't get Alice's record as result. Basically this goes for every relational operator (=,<,>,<>,between, like): i want to know if ANY of the results will satisfy my condition.
Is this possible without using
select xmlexists('/root/refNr[text() > 10 AND text() < 20]' PASSING BY REF column) FROM table;
Which requires my condition to be fumbled into the xpath expression, requiring placeholders and not beeing typesafe (what will this to with dates)?
thanks in advance,
BillDoor
No comments:
Post a Comment