this related soap xml response, , fetching data via oracle plsql.
the soap xml response (which contains multiple child nodes in contacts parent node) -
<firstname>abc</firstname> <lastname>xyz</lastname> <gender>m</gender> <service @effdate='2015-02-02'>active</service> <contacts> <contacttier><contactid>ct001</contactid><contactname>core contact</contactname></contacttier> <contacttier><contactid>ct015</contactid><contactname>contact 2</contactname></contacttier> </contacts> i have select query oracle apex report, directly utilizes soap xml response , displays fields -
select quota.firstname ,quota.lastname ,quota.gender ,quota.service ,quota.contacts.extract('//contacttier//contactname//text()','xmlns="http://grocery.org/schema/quota"').getstringval() name ,quota.contacts.extract('//contacttier//contactname//text()','xmlns="http://grocery.org/schema/quota"').getstringval() id xmltable ( xmlnamespaces( 'http://grocery.org/schema/quota/service' ,'http://grocery.org/schema/quota' ) ,'for $i in /*/*/*/quota/quota return /$i' passing (select xmlresp mytable id = 'soapid3') columns service xmltype path '/quota/v61:service' ,contacts xmltype path '/quota/v61:contacts' ,first_name varchar2(2000) path '/quota/v61:firstname' ,last_name varchar2(2000) path '/quota/lastname' ,gender varchar2(2000) path '/quota/gender' ) quota ; my concern extract function concatenates contactnames (from contacts node) , returns them part of 1 single row.
i need query return many number of rows there contacttiers.
(please ignore xmlnamespace , other aspects if seem wrong..) hope makes sense..
looking forward replies. in advance..
if need return many rows there contacts, you'll want more programmatic control on xml parsing. following, outputs 1 or more rows per employee (up how many contacts he/she has).
first, basic emp table:
create table my_emp ( empno number, ename varchar2(50), job varchar2(50), hiredate date, contactid number, contactname varchar2(50) ) now, pl/sql block process xml:
declare --l_bfile bfile; l_clob clob; l_parser dbms_xmlparser.parser; l_doc dbms_xmldom.domdocument; l_nl dbms_xmldom.domnodelist; l_cnl dbms_xmldom.domnodelist; l_n dbms_xmldom.domnode; l_cn dbms_xmldom.domnode; l_temp varchar2(1000); type tab_type table of my_emp%rowtype; t_tab tab_type := tab_type(); begin -- clob big xml document loaded file (or soap service) -- using following test document l_clob := '<?xml version="1.0" ?> <employees> <employee> <empno>123</empno> <ename>smith</ename> <job>clerk</job> <hiredate>17-dec-1980</hiredate> <contacts> <contacttier> <contactid>100</contactid> <contactname>joe blow</contactname> </contacttier> <contacttier> <contactid>200</contactid> <contactname>mary jane</contactname> </contacttier> </contacts> </employee> <employee> <empno>7499</empno> <ename>allen</ename> <job>salesman</job> <hiredate>20-feb-1981</hiredate> <contacts> <contacttier> <contactid>300</contactid> <contactname>donald duck</contactname> </contacttier> </contacts> </employee> <employee> <empno>1234</empno> <ename>jones</ename> <job>salesman</job> <hiredate>20-feb-1982</hiredate> </employee> </employees>'; -- implicit date conversions dbms_session.set_nls('nls_date_format','''dd-mon-yyyy'''); -- create parser. l_parser := dbms_xmlparser.newparser; -- parse document dbms_xmlparser.parseclob(l_parser, l_clob); l_doc := dbms_xmlparser.getdocument(l_parser); -- list of employee nodes in document l_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(l_doc),'/employees/employee'); cur_emp in 0 .. dbms_xmldom.getlength(l_nl)-1 loop l_n := dbms_xmldom.item(l_nl, cur_emp); t_tab.extend; dbms_xslprocessor.valueof(l_n,'empno',t_tab(t_tab.last).empno); dbms_xslprocessor.valueof(l_n,'ename',t_tab(t_tab.last).ename); dbms_xslprocessor.valueof(l_n,'job',t_tab(t_tab.last).job); dbms_xslprocessor.valueof(l_n,'hiredate',t_tab(t_tab.last).hiredate); -- list of contacts employee l_cnl := dbms_xslprocessor.selectnodes(l_n, 'contacts/contacttier'); -- add first contact, if l_cn := dbms_xmldom.item(l_cnl, 0); dbms_xslprocessor.valueof(l_cn,'contactid',t_tab(t_tab.last).contactid); dbms_xslprocessor.valueof(l_cn,'contactname',t_tab(t_tab.last).contactname); -- add other contacts, repeating employee info each row cur_contact in 1 .. dbms_xmldom.getlength(l_cnl)-1 loop l_cn := dbms_xmldom.item(l_cnl, cur_contact); t_tab.extend; dbms_xslprocessor.valueof(l_n,'empno',t_tab(t_tab.last).empno); dbms_xslprocessor.valueof(l_n,'ename',t_tab(t_tab.last).ename); dbms_xslprocessor.valueof(l_n,'job',t_tab(t_tab.last).job); dbms_xslprocessor.valueof(l_n,'hiredate',t_tab(t_tab.last).hiredate); dbms_xslprocessor.valueof(l_cn,'contactid',t_tab(t_tab.last).contactid); dbms_xslprocessor.valueof(l_cn,'contactname',t_tab(t_tab.last).contactname); end loop; end loop; -- insert data emp table table collection. --forall in t_tab.first .. t_tab.last -- insert my_emp values t_tab(i); --commit; ---------------------------------------- -- use following testing stdout ---------------------------------------- in t_tab.first .. t_tab.last loop dbms_output.put_line(t_tab(i).empno || ', ' || t_tab(i).ename || ', ' || t_tab(i).job || ', ' || t_tab(i).hiredate || ', ' || t_tab(i).contactid || ', ' || t_tab(i).contactname); end loop; ----------------------------------------- -- free resources dbms_lob.freetemporary(l_clob); dbms_xmldom.freedocument(l_doc); dbms_xmlparser.freeparser(l_parser); exception when others dbms_lob.freetemporary(l_clob); dbms_xmlparser.freeparser(l_parser); dbms_xmldom.freedocument(l_doc); end; the output is:
123, smith, clerk, 17-dec-1980, 100, joe blow 123, smith, clerk, 17-dec-1980, 200, mary jane 7499, allen, salesman, 20-feb-1981, 300, donald duck 1234, jones, salesman, 20-feb-1982, , also see this article , site reference things oracle.
Comments
Post a Comment