title: PL/SQL XML parsing into relational tables
body: <p>I am using a PL/SQL procedure for inserting values from XML to relational tables. The XML file resides in an XMLTYPE column. <br/>
  Columns of table (OFFLINE_XML) containing XML are <br/></p>
  
  <pre><code>ID, XML_FILE, STATUS
  </code></pre>
  
  <p>There are two table in which i want to insert the values i.e. DEPARTMENT and SECTIONS
  Structure of DEPARTMENT is as under:-</p>
  
  <pre><code>ID, NAME
  </code></pre>
  
  <p>Structure of SECTIONS table is:-</p>
  
  <pre><code>ID, NAME, DEPARTMENT_ID
  </code></pre>
  
  <p>Now there is a third table (LIST_1) in which i want to insert the values which already exists in both the above mentioned tables.<br/>
  Structure of LIST_1 is :-</p>
  
  <pre><code>ID, DEPARTMENT_ID,DEPARTMENT_NAME,SECTIONS_ID, SECTIONS_NAME
  </code></pre>
  
  <p>XML format is as under:-</p>
  
  <pre><code>&lt;ROWSET&gt; 
   &lt;DEPARTMENT&gt;
    &lt;DEPARTMENT_ID&gt;DEP22681352268280797&lt;/DEPARTMENT_ID&gt;
    &lt;DEPARTMENT_NAME&gt;myDEPARTMENT&lt;/DEPARTMENT_NAME&gt;
    &lt;SECTIONS_ID&gt;6390135666643567&lt;/SECTIONS_ID&gt;
    &lt;SECTIONS_NAME&gt;mySection&lt;/SECTIONS_NAME&gt;
   &lt;/DEPARTMENT&gt;
   &lt;DEPARTMENT&gt;
    &lt;DEPARTMENT_ID&gt;DEP255555555550797&lt;/DEPARTMENT_ID&gt;
    &lt;DEPARTMENT_NAME&gt;myDEPARTMENT2&lt;/DEPARTMENT_NAME&gt;
    &lt;SECTIONS_ID&gt;63901667779243567&lt;/SECTIONS_ID&gt;
    &lt;SECTIONS_NAME&gt;mySection2&lt;/SECTIONS_NAME&gt;
   &lt;/DEPARTMENT&gt;
  &lt;/ROWSET&gt;
  
  
  DECLARE
    BEGIN
      insert all
          into department (id, name)
             values (unit_id, unit_name)
          into sections (id, name, department _id)
            values ( sect_id, sect_name, department _id)
      select department .id as department _id
            , department.name as department_name
             , sect.id as sect_id
            , sect.name as sect_name
      from OFFLINE_XML
           , xmltable('/ROWSET/DEPARTMENT'
                     passing OFFLINE_XML.xml_file
                     columns
                        "ID"  varchar2(20) path 'UNIT_ID'
                        , "NAME"  varchar2(20) path 'UNIT_NAME'
                    ) department
            , xmltable('/ROWSET/DEPARTMENT'
                 passing OFFLINE_XML.xml_file
                 columns
                    "ID"  varchar2(20) path 'SECTIONS_ID'
                    , "NAME"  varchar2(20) path 'SECTIONS_NAME'
                ) sect
                 where status = 3;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
      dbms_output.put_line('Duplicate='|| department.id );
      --insert into LIST_1 values(ID,DEPARTMENT_ID, SECTIONS_ID, DEPARTMENT_NAME,SECTIONS_NAME);
  END;
  </code></pre>
  
  <p>Now the problem is that how can i insert or identify the values on the basis of primary key which already exists in table DEPARTMENT and SECTIONS and thereafter insert the existing values in LIST_1 table.
  <strong>------An updated effort --------------</strong></p>
  
  <p>I came up with another solution but this again is giving me problem. In the under mentioned procedure cursor tends to repeat for every xquery. I don't know how am i going to handle this issue..</p>
  
  <pre><code>DECLARE
      department_id varchar2(20);
      department_name varchar2(20);
      sect_id varchar2(20);
      sect_name varchar2(20);
      sections_unit_id varchar2(20);
      var number;
      CURSOR C1 IS
       select
              sect.id as sect_id
            , sect.name as sect_name
            , sect.unit_id  as sections_unit_id
      from OFFLINE_XML
             , xmltable('/ROWSET/DEPARTMENT'
                     passing OFFLINE_XML.xml_file
                     columns
                        "ID"  varchar2(20) path 'UNIT_ID'
                        , "NAME"  varchar2(20) path 'UNIT_NAME'
                    ) DEPARTMENT
            , xmltable('/ROWSET/DEPARTMENT'
                 passing OFFLINE_XML.xml_file
                 columns
                    "ID"  varchar2(20) path 'SECTIONS_ID'
                    , "NAME"  varchar2(20) path 'SECTIONS_NAME'
                    , "DEPARTMENT_ID" varchar2(20) path 'DEPARTMENT_ID'
                ) sect
                 where status = 3;
  
  BEGIN
        FOR R_C1 IN C1 LOOP
        BEGIN
        var :=1;
           --insert into sections_temp_1 (id, name)values ( R_C1.sect_id, R_C1.sect_name);
           -- commit;
             dbms_output.put_line('Duplicate='||var);
        EXCEPTION
           WHEN DUP_VAL_ON_INDEX THEN
           dbms_output.put_line('Duplicate='||R_C1.sect_id);
        END;
        var:=var+1;
        END LOOP;
  
  END;
  </code></pre>