Can dbms_lob.instr have end position?
647927Jul 2 2008 — edited Jul 8 2008I am extracting data from xml using dbms_lob utility and the pattern in XML is as below.
<Atag>
<Btag>
<Ctag>value</Ctag>
</Btag>
<Btag>
<Ctag>value</Ctag>
<Dtag>value</Dtag>
</Btag>
<Btag>
<Ctag>value</Ctag>
<Dtag>value</Dtag>
</Btag>
</Atag>
I want to extract all tag values inside <Btag> which will be nohting but Ctag and Dtag values. There can be any occurence of <Btag> in this xml. When I use like this,
val:=0;
<LOOP>
val1:=dbms_lob.instr(xml,'<Btag>',val);
val2:=dbms_lob.instr(xml,'</Btag>,val1);
val3:=case(val1) when 0 then 0 else dbms_lob.instr(xml,'<Ctag>,val1) end;
val4:=case(val3) when 0 then 0 else dbms_lob.instr(xml,'</Ctag>,val3) end;
val5:=case(val1) when 0 then 0 else dbms_lob.instr(xml,'<Dtag>,val1) end;
val6:=case(val5) when 0 then 0 else dbms_lob.instr(xml,'</Dtag>,val5) end;
In this case as <Dtag> doesnt exist inside the first occurence of <Btag>, so it gets the value from the next one which is not what I require. I want that to be set to null/zero so for which I want to search only between the first occurence of <Btag> and </Btag>.
I know it is confusing but can some one of you tell me how to go and find the values of <Ctag> and <Dtag> between each occurence of <Btag>. If they are not present I want the value to be set to zero/null.
Thanks in advance.