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;
Comments
Post a Comment