SQL & PL/SQL

Hello Experts,

I have 3 levels of XML of which I need to extract all the fields. I am trying to extract them using collections based on the cursor %ROWTYPE.
The sample XML is available in the variable L_xml.

Could you please suggest the best way to extract them.

My Code:

set serveroutout on
DECLARE
c12 clob;
c13 xmltype;
c14 xmltype;
CURSOR c1
IS
  SELECT EXTRACTVALUE(value(x),'/t/t1/item') item,
         EXTRACTVALUE(value(x),'/t/t1/desc') item_desc,
         EXTRACTVALUE(value(x),'/t/t1/supplier/item_supplier') supplier,
         EXTRACT(value(x),'/t/t1/supplier/country') country_xml
    FROM TABLE(XMLSEQUENCE(EXTRACT(c12,'/t'))) x;
type c1_t is table of c1%rowtype;
c1_tab c1_t;
CURSOR c2
IS
  SELECT EXTRACTVALUE(value(x),'/country/supplier_country') channel,
         EXTRACT(value(x),'/country/dimension') dimension_xml
    FROM TABLE(XMLSEQUENCE(EXTRACT(c13,'/country'))) x;
type c2_t is table of c2%rowtype;
c2_tab c2_t;
CURSOR c3
IS
  SELECT EXTRACTVALUE(value(x),'/dimension/unit') unit,
         EXTRACTVALUE(value(x),'/dimension/item_height') item_height,
         EXTRACTVALUE(value(x),'/dimension/item_width') item_width
    FROM TABLE(XMLSEQUENCE(EXTRACT(c14,'/dimension'))) x;
type c3_t is table of c3%rowtype;
c3_tab c3_t;
L_xml VARCHAR2(2000) := '<?xml version="1.0" encoding="UTF-8"?>
<t>
   <t1>
       <item>1234</item>
       <desc>asdfg</desc>
       <supplier>
            <item_supplier>3456</item_supplier>
            <country>
               <supplier_country>BB</supplier_country>
               <dimension>
                  <unit>metre</unit>
                  <item_height>23</item_height>
                  <item_width>230</item_width>
               </dimension>
               <supplier_country>SS</supplier_country>
               <dimension>
                  <unit>metre</unit>
                  <item_height>21</item_height>
                  <item_weight>210</item_weight>
               </dimension>
            </country>
       </supplier>     
   </t1>
</t>';
BEGIN
c12 := xmltype(L_xml);
for i in 1..c1_tab.COUNT
loop
    dbms_output.put_line('C1');
    c13 := c1_tab(i).country_xml;
    for j in 1..c2_tab.COUNT
    loop
    dbms_output.put_line('C2');
    c14:=c2_tab(j).dimension_xml;
        for k in 1..c3_tab.COUNT
        loop
        dbms_output.put_line('C3');
        end loop;
    end loop;
end loop;
END;
/

Error :

Error report:

ORA-06550: line 13, column 28:

PL/SQL: ORA-00932: inconsistent datatypes: expected - got -

ORA-06550: line 9, column 3:

PL/SQL: SQL Statement ignored

ORA-06550: line 7, column 8:

PLS-00341: declaration of cursor 'C1' is incomplete or malformed

ORA-06550: line 15, column 1:

PL/SQL: Item ignored

ORA-06550: line 64, column 8:

PLS-00382: expression is of wrong type

ORA-06550: line 64, column 1:

PL/SQL: Statement ignored

ORA-06550: line 69, column 22:

PLS-00487: Invalid reference to variable 'C1%ROWTYPE'

ORA-06550: line 69, column 5:

PL/SQL: Statement ignored

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

Thanks,

Haider