bind variable in xpath expression

Posted on 2008-11-03
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
    LVL 73

    Accepted Solution

    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

    this works like a charm, thanks much for the help
    LVL 73

    Expert Comment

    glad I could help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now