?
Solved

bind variable in xpath expression

Posted on 2008-11-03
3
Medium Priority
?
1,226 Views
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,
   
)

<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
0
Comment
Question by:wxII
  • 2
3 Comments
 
LVL 74

Accepted Solution

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

0
 

Author Comment

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

Expert Comment

by:sdstuber
ID: 22883481
glad I could help
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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