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,

  <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"/>

previously on hardcoded statements.

        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, sqldeveloper 1.5 and solely in PL/SQL environment.

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);

wxIIAuthor Commented:
this works like a charm, thanks much for the help
glad I could help
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.