bind variable in xpath expression

Posted on 2008-11-03
Medium Priority
Last Modified: 2013-12-07
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.

Question by:wxII
  • 2
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 22875925
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);


Author Comment

ID: 22883369
this works like a charm, thanks much for the help
LVL 74

Expert Comment

ID: 22883481
glad I could help

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month16 days, 17 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question