PL/SQL XML parsing into relational tables

Question

I am using a PL/SQL procedure for inserting values from XML to relational tables. The XML file resides in an XMLTYPE column.
Columns of table (OFFLINE_XML) containing XML are

ID, XML_FILE, STATUS

There are two table in which i want to insert the values i.e. DEPARTMENT and SECTIONS Structure of DEPARTMENT is as under:-

ID, NAME

Structure of SECTIONS table is:-

ID, NAME, DEPARTMENT_ID

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.
Structure of LIST_1 is :-

ID, DEPARTMENT_ID,DEPARTMENT_NAME,SECTIONS_ID, SECTIONS_NAME

XML format is as under:-

<ROWSET> 
 <DEPARTMENT>
  <DEPARTMENT_ID>DEP22681352268280797</DEPARTMENT_ID>
  <DEPARTMENT_NAME>myDEPARTMENT</DEPARTMENT_NAME>
  <SECTIONS_ID>6390135666643567</SECTIONS_ID>
  <SECTIONS_NAME>mySection</SECTIONS_NAME>
 </DEPARTMENT>
 <DEPARTMENT>
  <DEPARTMENT_ID>DEP255555555550797</DEPARTMENT_ID>
  <DEPARTMENT_NAME>myDEPARTMENT2</DEPARTMENT_NAME>
  <SECTIONS_ID>63901667779243567</SECTIONS_ID>
  <SECTIONS_NAME>mySection2</SECTIONS_NAME>
 </DEPARTMENT>
</ROWSET>


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;

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. ------An updated effort --------------

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..

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;

Answer 1

Seems that first of all you need a little bit more complicated XQuery to extract rows from XMLType field.

There are no need to extract sections and departments separately and after that try to match it back.

Try this variant:

select 
  department_id,
  department_name,
  sections_id,
  sections_name
from 
  OFFLINE_XML xml_list,
  xmltable(
    '
      for $dept in $param/ROWSET/DEPARTMENT
        return $dept
    '
    passing xml_list.xml_file as "param"
    columns
      "DEPARTMENT_ID"   varchar2(100) path '//DEPARTMENT/DEPARTMENT_ID',
      "DEPARTMENT_NAME" varchar2(4000) path '//DEPARTMENT/DEPARTMENT_NAME',
      "SECTIONS_ID"     varchar2(100) path '//DEPARTMENT/SECTIONS_ID',
      "SECTIONS_NAME"   varchar2(4000) path '//DEPARTMENT/SECTIONS_NAME'
  ) section_list
where 
  xml_list.Status = 3

SQL fiddle - 1

After that you got a dataset which can be outer joined to existing tables on it's primary keys (or something other - depends on required logic) if you want to find if any values already exists:

select 
  offline_set.offline_xml_id,
  offline_set.department_id,
  offline_set.department_name,
  offline_set.sections_id,
  offline_set.sections_name,
  nvl2(dept.id,'Y', 'N') is_dept_exists, 
  nvl2(sect.id,'Y', 'N') is_sect_exists 
from 
  (  
    [... skipped text of previous query ...]
  ) offline_set,
  department dept,
  sections   sect
where
  dept.id (+) = offline_set.department_id
  and
  sect.id (+) = offline_set.sections_id

SQL fiddle - 2

Because I actually unaware about logic behind this requirements, I can't suggest any future processing instructions. But it seems that you missed reference to OFFLINE_XML table in LIST_1 which needed to identify source of errors/duplicates.