bind variable in xpath expression

hi there,

i've successfully tried to execute these hardcoded value with my xml data in xmltype columns. but in my stored program when i substitute the variable within xpath, it returns me nothing.

eg xml documents

table created with these value

table bststruct(
    nmstruct xmltype,
   
)

<country>
  <id>MAL</id>
  <sponsornode spacctid="100000" spacctcode="100000" level="0" legno="1">
    <sponsornode spacctid="NSH" spacctcode="nscode" level="1" legno="1"/>
    <sponsornode spacctid="WXII" spacctcode="wxcode" level="1" legno="2"/>
    <sponsornode spacctid="JOH" spacctcode="jhcode" level="1" legno="3"/>
    <sponsornode spacctid="SNK" spacctcode="sncode" level="1" legno="4"/>
    <sponsornode spacctid="KEN" spacctcode="kncode" level="1" legno="5"/>
  </sponsornode>
</country>

previously on hardcoded statements.

  DECLARE
        v_upline_level INTEGER := 0;
        v_upline_child_legno INTEGER := 0;

  select extractValue(nmstruct,'//sponsornode[@spacctid="100000"]/@level') INTO v_upline_level from bststruct;

  select count(*) INTO v_upline_child_legno
  from bststruct,xmltable('//sponsornode[@spacctid="100000"]/child::node()' passing nmstruct);

  it executed perfectly. but when i substitute the value of 100000 above to parameter passed from outside to my stored program




  select extractValue(nmstruct,'//sponsornode[@spacctid=":p_placed_under_spacct_id"]/@level') INTO v_upline_level from bststruct;

  select count(*) INTO v_upline_child_legno
  from bststruct,xmltable('//sponsornode[@spacctid=":p_placed_under_spacct_id"]/child::node()' passing nmstruct)
  where countryid = p_country_id;

  v_upline_level is "blank"
  v_upline_child_legno return "0"

  isn't xpath a string?

  the document is deeply nested element xml. please advise me on this, it's really getting me no where..
  my environment is oracle 11g 11.1.0.6, sqldeveloper 1.5 and solely in PL/SQL environment.

  wxII
wxIIAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
Put the entire xpath into your bind variable.


by embedding the bind inside the string, it's no longer a variable at all, but part of the string. you are literally looking for the string ":p_placed_under_spacct_id"
which, of course, you don't have.


try this instead.


p_placed_under_spacct_id := '//sponsornode[@spacctid="100000"]/child::node()';

select count(*) INTO  v_upline_child_legno
  from bststruct,xmltable( p_placed_under_spacct_id  passing nmstruct);

0
 
wxIIAuthor Commented:
this works like a charm, thanks much for the help
0
 
sdstuberCommented:
glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.