oracle - Cursor for loop creating duplicate entries for nested XQUERY -

i have created procedure parsing xml data multiple tables. catching exception primary key constraint, , if there duplicate found in result, inserted table named duplicate.
when use cursor tends iterate more required number of times i.e. 1.
procedure

declare per_id varchar2(20);     name varchar2(20); sections_id varchar2(20); sections_name varchar2(20);     var1 number;     exception_var number; cursor c1     select d.department_id        , d.department_name        , s.sections_id       , s.sections_name    xml_unit_download t      , xmltable(          '/rowset/data'          passing t.xml_file          columns            department_id   varchar2(20) path 'department/department_id'          , department_name varchar2(30) path 'department/department_name'          , sections        xmltype      path 'sections'        ) d      , xmltable(          '/sections'          passing d.sections          columns            sections_id     varchar2(20) path 'sections_id'         , sections_name   varchar2(30) path 'sections_name'       ) s    t.status = 4;   begin    r_c1 in c1 loop       begin       insert department(id, name) values(r_c1.per_id, r_c1.name);       insert sections(id, name) values(r_c1.sections_id, r_c1.sections_name);       var1:= var1+1;        dbms_output.put_line('insert=' || var1);       commit;            --dbms_output.put_line('duplicate='||var);       exception          when dup_val_on_index          dbms_output.put_line('duplicate=');          insert duplicate(id, name)values(r_c1.id, r_c1_name);       end;       end loop;   end; 

how going handle situation? have tried using insert not seem work. here try of insert procedure

declare per_id varchar2(20);     name varchar2(200);     var1 number;     exception_var number;    begin        insert       sections (id) values(department_id)        --into sect (id, name) values(s.sections_id, s.sections_name )    select d.department_id        , d.department_name        , s.sections_id       , s.sections_name    xml_unit_download t      , xmltable(          '/rowset/data'          passing t.xml_file          columns            "department_id"   varchar2(20) path 'department/department_id'          , "department_name" varchar2(30) path 'department/department_name'          , "sections"        xmltype      path 'sections'        ) d      , xmltable(          '/sections'          passing d.sections          columns            "sections_id"     varchar2(20) path 'sections_id'         , "sections_name"   varchar2(30) path 'sections_name'       ) s    t.status = 4;   dbms_output.put_line('insert=' || var1);       var1:= var1+1;        dbms_output.put_line('insert=' || var1);       commit;            --dbms_output.put_line('duplicate='||var);       exception          when dup_val_on_index          --insert          dbms_output.put_line('duplicate=');   end; 

the xml being queried contains data of department , sections. department has 1 many relationship sections i.e. department can have 1 or multiple sections , there may instances department not have sections.

structure of xml such tag identifies department , set of corresponding sections.
xml

<rowset>  <data>  <department>   <department_id>dep1</department_id>   <department_name>mydepartment1</department_name>  </department>  <sections>   <sections_id>6390135666643567</sections_id>   <sections_name>mysection1</sections_name>   </sections>    <sections>   <sections_id>6390135666643567</sections_id>   <sections_name>mysection2</sections_name>   </sections>  </data>  <data>  <department>   <department_id>dep2</department_id>   <department_name>mydepartment2</department_name>  </department>  <sections>   <sections_id>63902</sections_id>   <sections_name>mysection1</sections_name>   </sections>  </data> <data>  <department>   <department_id>dep3</department_id>   <department_name>mydepartment3</department_name>  </department> </data> </rowset> 

since can have multiple sections in each department, you'll expect duplicates. might able want moving catch exception, still insert sections:

  r_c1 in c1 loop       begin          insert department(id, name)             values(r_c1.department_id, r_c1.department_name);       exception          when dup_val_on_index          dbms_output.put_line('duplicate=');          insert duplicate(id, name)             values(r_c1.department_id, r_c1.department_name);       end;       insert sections(id, name)          values(r_c1.sections_id, r_c1.sections_name);       var1:= var1+1;       dbms_output.put_line('insert=' || var1);    end loop; 

you use tracker variables (if see record same department_id previous 1 saw, don't attempt insert department record, sections insert), or use nested loops:

declare    cursor dept_cur       select d.department_id          , d.department_name          , d.sections       xml_unit_download t          , xmltable(             '/rowset/data'             passing t.xml_file             columns               "department_id"   varchar2(20) path 'department/department_id'             , "department_name" varchar2(30) path 'department/department_name'             , "sections"        xmltype      path 'sections'          ) d                t.status = 4;     cursor sect_cur(sections xmltype)       select s.sections_id          , s.sections_name       xmltable(             '/sections'             passing sections             columns               "sections_id"     varchar2(20) path 'sections_id'            , "sections_name"   varchar2(30) path 'sections_name'          ) s; begin    dept in dept_cur loop       insert department(id, name)          values (dept.department_id, dept.department_name);       sect in sect_cur(dept.sections) loop          insert sections(id, name, department_id)             values (sect.sections_id, sect.sections_name, dept.department_id);       end loop;    end loop; end; /  pl/sql procedure completed. 

this uses 1 loop department info (which won't have duplicates) plus xmltype sections, , passes section second cursor expand.

select * department;  id                             name ------------------------------ ------------------------------ dep1                           mydepartment1 dep2                           mydepartment2 dep3                           mydepartment3  select * sections;  id                             name                           department_id ------------------------------ ------------------------------ ------------------------------ 6390135666643567               mysection1                     dep1 6390135666643567               mysection2                     dep1 63902                          mysection1                     dep2 

you don't have use pl/sql, 2 inserts:

insert department(id, name) select d.department_id    , d.department_name xml_unit_download t    , xmltable(       '/rowset/data'       passing t.xml_file       columns         "department_id"   varchar2(20) path 'department/department_id'       , "department_name" varchar2(30) path 'department/department_name'    ) d    t.status = 4; 

... and:

insert sections(id, name, department_id) select s.sections_id    , s.sections_name    , d.department_id xml_unit_download t    , xmltable(       '/rowset/data'       passing t.xml_file       columns          "department_id"   varchar2(20) path 'department/department_id'       , "department_name" varchar2(30) path 'department/department_name'       , "sections"        xmltype      path 'sections'    ) d    , xmltable(       '/sections'       passing d.sections       columns          "sections_id"     varchar2(20) path 'sections_id'      , "sections_name"   varchar2(30) path 'sections_name'    ) s    t.status = 4; 

... put same data in tables pl/sql block does.

in both i've assumed want column linking 2 tables, perhaps aren't uniquely linked , want separate section , department_section tables, generated in same way.

also note both approaches create department record dep3, original wouldn't do, unless used outer joins previous answer; , you'd have notice there no section info , not attempt second insert.