XML Oracle: Multiple Child Node Extract

Question

I have an xml code:

<begin> <entry> <lastname>gordon</lastname> <NumberList> <number>100</number> <codelist> <code>213</code> <code>214</code> <codelist> <login> <user>user1</user> <user>user2</user> </login> <NumberList> <address> <addresslist>Jl. jalan pelan-pelan ke Bekasi, Indonesia</addresslist> </address> </entry> <entry> <lastname>mark</lastname> <address> <addresslist>Jl. jalan cepet-cepet ke Jakarta, Indonesia</addresslist> </address> </entry> </begin> 

my code is:

 FOR r IN (SELECT VALUE(p) col_val, EXTRACT(VALUE(P), '/entry/codelist') AS code, EXTRACT(VALUE(P), '/entry/login') AS login FROM TABLE(XMLSequence(Extract(x,'/begin/entry'))) p) LOOP IF r.col_val.existsnode('/entry/lastname/text()') > 0 THEN vc_lastname := r.col_val.extract('/sdnEntry/lastname/text()').getstringval(); END IF; IF r.col_val.existsnode('/entry/address/addresslist/text()') > 0 THEN vc_address := r.col_val.extract('/sdnEntry/address/addresslist/text()').getstringval(); END IF; IF r.col_val.existsnode('/entry/codelist/id/code/text()') > 0 AND r.col_val.existsnode('/entry/login/user/text()') > 0 THEN FOR R1 IN (SELECT EXTRACTVALUE(VALUE(T1), '/codelist/code/text()') AS code FROM TABLE(XMLSEQUENCE(EXTRACT(R.code, '/codelist'))) T1) LOOP DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R1.code||' - '||R2.address); END LOOP; FOR R2 IN (SELECT EXTRACTVALUE(VALUE(T1), '/login/user/text()') AS user FROM TABLE(XMLSEQUENCE(EXTRACT(R.address, 'login/'))) T1) LOOP DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R2.user||' - '||R2.address); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName); END IF; 

My problem: how to loop child nodes so that the data becomes like this:

 LastName | Number | code | user | address gordon | 100 | 213 | user1 |Jl. jalan pelan-pelan ke Bekasi, Indonesia gordon | 100 | 213 | user2 |Jl. jalan pelan-pelan ke Bekasi, Indonesia gordon | 100 | 214 | user1 |Jl. jalan pelan-pelan ke Bekasi, Indonesia gordon | 100 | 214 | user2 |Jl. jalan pelan-pelan ke Bekasi, Indonesia mark | Null | null | null |Jl. jalan cepet-cepet ke Jakarta, Indonesia 

Any help would be appreciated.

Answer 1

You can achieve the desired result using the XMLTable() function:

 select q.Lastname , q.Numberid , s.codeid , w.LoginId , q.address from t1 t left join xmltable('/begin/entry' passing t.xml_col columns LastName varchar2(21) path 'lastname', NumberId number path 'NumberList/number', Address varchar2(201) path 'address/addresslist', CodeList XmlType Path 'NumberList/codelist/code', Logins XmlType Path 'NumberList/login/user' ) q on (1=1) left join xmltable('/code' passing q.CodeList columns CodeId number path '.') s on (1=1) left join xmltable('/user' passing q.Logins columns LoginId varchar2(11) path '.') w on (1=1) 

Result: SQLFiddle Demo

 Lastname Numberid Codeid Loginid Address --------------------------------------------------------------------------- gordon 100 213 user1 Jl. jalan pelan-pelan ke Bekasi, Indonesia gordon 100 213 user2 Jl. jalan pelan-pelan ke Bekasi, Indonesia gordon 100 214 user1 Jl. jalan pelan-pelan ke Bekasi, Indonesia gordon 100 214 user2 Jl. jalan pelan-pelan ke Bekasi, Indonesia mark null null null Jl. jalan cepet-cepet ke Jakarta, Indonesia 

Learn more about the XMLTable() function.

Note When working with Oracle releases prior to 11.2.0.2, you may encounter an ORA-1780 error (error 8545377) for certain types of XML queries if cursor_sharing set to FORCE or SIMILAR (deprecated starting from 11.2). Setting cursor_sharing to EXACT (the default value) will solve the problem.