oracle - Parsing unknown number nodes of a XML with namespace in PL/SQL procedure? -

i have xml_data passing procedure-

<?xml version="1.0" encoding="utf-8" standalone="yes"?> <ns2:a xmlns:ns2="http://www.sbc.com/iag/schemas/adapters" xmlns="http://www.sbc.com/iag/schemas/core">      <ns2:b>          <ns2:ordernumber>99995</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>      </ns2:b>     <ns2:b>          <ns2:ordernumber>99699</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>      </ns2:b>     <ns2:b>          <ns2:ordernumber>69999</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>      </ns2:b     <ns2:b>          <ns2:ordernumber>67999</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>      </ns2:b> </ns2:a> 

node

ns2:b

can repeated number of times , not fixed. how fetch

ns2:ordernumber

from xml data in oracle procedure.i able fetch value if

ns2:b(ns2:ordernumber)

occurs 1 time through below code-

select extractvalue(   xml_data,     xmlns="http://www.sbc.com/iag/schemas/core"' )   '/ns2:a/ns2:b/ns2:ordernumber/text()',   'xmlns:ns2="http://www.sbc.com/iag/schemas/adapters", order_number dual;    dbms_output.put_line('failedretry -' ||order_number); 

but how keep iterating until values of node

in xml.i have tried different solution posted on site,but none worked.

use xmltable.

select y.*  dual, xmltable( xmlnamespaces(default 'http://www.sbc.com/iag/schemas/core', 'http://www.sbc.com/iag/schemas/adapters' "ns2"), 'ns2:a/ns2:b' passing xml_data columns ordernumber varchar2(10) path 'ns2:ordernumber',     serviceordernumber varchar2(10) path 'ns2:serviceordernumber' ) y; 

sample query:

sql> x(xml_data) (         select xmltype('<?xml version="1.0" encoding="utf-8" standalone="yes"?> <ns2:a xmlns:ns2="http://www.sbc.com/iag/schemas/adapters" xmlns="http://www.sbc.com/iag/schemas/core">      <ns2:b>         <ns2:ordernumber>99995</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>     </ns2:b>     <ns2:b>         <ns2:ordernumber>99699</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>     </ns2:b>     <ns2:b>         <ns2:ordernumber>69999</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>     </ns2:b>     <ns2:b>         <ns2:ordernumber>67999</ns2:ordernumber>         <ns2:serviceordernumber>88888</ns2:serviceordernumber>     </ns2:b> </ns2:a>') dual ) select y.*  x, xmltable( xmlnamespaces(default 'http://www.sbc.com/iag/schemas/core', 'http://www.sbc.com/iag/schemas/adapters' "ns2"), 'ns2:a/ns2:b' passing xml_data columns ordernumber varchar2(10) path 'ns2:ordernumber',         serviceordernumber varchar2(10) path 'ns2:serviceordernumber' ) y;  ordernumbe serviceord ---------- ---------- 99995      88888 99699      88888 69999      88888 67999      88888  elapsed: 00:00:00.03 sql>  

if want iterate each record, use in cursor.

decare     cursor c1 select y.*      dual,     xmltable( xmlnamespaces(default 'http://www.sbc.com/iag/schemas/core', 'http://www.sbc.com/iag/schemas/adapters' "ns2"), 'ns2:a/ns2:b'     passing xml_data     columns ordernumber varchar2(10) path 'ns2:ordernumber',         serviceordernumber varchar2(10) path 'ns2:serviceordernumber'     ) y; begin     in c1     loop         dbms_output.put_line('failedretry -' ||i.ordernumber || ' '|| i.serviceordernumber);     end loop; end;