oracle xmlexists with variable path

Question

I have a table tsk with 2 columns : a task id in number, and a task scope in XMLTYPE which can be like :

<scope>
    <siteCode>2</siteCode>
    <supplierCode>1111</supplierCode>
    <contractCode>464</contractCode>
    <orderNumber>85235478</orderNumber>
</scope>

But the elements under the tag may vary from one record to another

I need to select task ids which match some conditions in the scope. For example :

select tskid
from tsk t 
where xmlexists('$a/scope[siteCode = 2 and supplierCode = 111]' passing t.tskscope as "a");

As the scope may vary, I have a PL/SQL function taking a xml path p_xmlpath to look for, in varchar2 type. So for example, p_xmlpath will be :

p_xmlpath := 'scope[siteCode = 2 and supplierCode = 1111]';

Then I want to run the query with XMLEXISTS to find the matching records. I thought to do it with bind variables in the following way :

select tskid
from tsk t 
where xmlexists('$a/$b' passing t.tskscope as "a", p_xmlpath as "b" );

By doing this, the query returns all records, without taking the condition with xmlexists.

Does someone know how to manage this, i.e. having a variable path to give to XMLEXISTS ?

Additional info : until now, I used the function existsNode and the following query was doing the job correctly:

select tskid
from tsk t 
where existsnode(t.tskscope, p_xmlpath) = 1;

But on one hand existsNode is deprecated and on the other I noticed that in my situation the function xmlexists was noticeably faster than existsNode, that's why I would to switch to xmlexists.

Thanks in advance.

Answer 1

I don't think you can; it seems to only allow the search values to be variables, not the whole search condition.

As a workaround you could build the XPath at runtome with replace():

select tskid
from tsk t 
where xmlexists(replace('$a/$b', '$b', p_xmlpath) passing t.tskscope as "a");

or build the XPath string as a variable if you'd rather not have a replace in there, and include the $a/ part:

p_xmlpath := '$a/' || p_xmlpath;

select tskid
from tsk t 
where xmlexists(p_xmlpath passing t.tskscope as "a");