XML : postgres XML-query with multiple results

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