Exception handling while processing XML

Question

I have an XML format as below.

<ROWSET>
  <ROW NUM="1">
    <pid>1</pid>
    <lon>25.3032016</lon>
    <lat>54.7146368</lat>
    <dt>20/10/2013 08:45:42 PM</dt>
  </ROW>
  <ROW NUM="2">
    <pid>1</pid>
    <lon>25.3032016</lon>
    <lat>54.7146368</lat>
    <dt>20/10/2013 08:45:47 PM</dt>
  </ROW>
  <ROW NUM="3">
    <pid>1</pid>
    <lon>25.3032016</lon>
    <lat>54.7146368</lat>
    <dt>20/10/2013 08:45:52 PM</dt>
  </ROW>
<ROW NUM="4">
    <pid>1</pid>
    <lon>25.3032016</lon>
    <lat>54.7146368</lat>
    <dt>20/10/2013 08:45:57 PM</dt>
  </ROW>
  <ROW NUM="5">
    <pid>1</pid>
    <lon>2512.3032016</lon> --Error part (as the longitude fields only stores NUMBER (13,10))
    <lat>54.7146368</lat>
    <dt>20/10/2013 08:45:57 PM</dt>
  </ROW>  
</ROWSET>

So far i have the following Procedure to load the XML into table,

PROCEDURE load_xml(i_xml CLOB) 
AS
     l_sqlerrm VARCHAR2(200);

  BEGIN

       INSERT INTO xml_stage (pid, longitude, latitude, date_time)
            (SELECT  EXTRACTVALUE(VALUE(row_list), '//pid') AS pid
                    ,EXTRACTVALUE(VALUE(row_list), '//lon') AS lon
                    ,EXTRACTVALUE(VALUE(row_list), '//lat') AS lat
                    ,EXTRACTVALUE(VALUE(row_list), '//dt') AS dt
             FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(i_xml), '/ROWSET/ROW'))) row_list
            );
       COMMIT;     
  EXCEPTION WHEN OTHERS THEN

      FOR rec IN
      (SELECT  EXTRACTVALUE(VALUE(row_list), '//pid') AS pid
              ,EXTRACTVALUE(VALUE(row_list), '//lon') AS lon
              ,EXTRACTVALUE(VALUE(row_list), '//lat') AS lat
              ,EXTRACTVALUE(VALUE(row_list), '//dt') AS dt
       FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(i_xml), '/ROWSET/ROW'))) row_list
      )
      LOOP
           BEGIN
                INSERT INTO xml_stage (pid, longitude, latitude, date_time)
                     VALUES (rec.pid, rec.lon, rec.lat, rec.dt);

           EXCEPTION WHEN dup_val_on_index THEN
                NULL;               
           WHEN others THEN
                l_sqlerrm := substr(SQLERRM,1,200);
                INSERT INTO errored_xml (error_id, date_time, error_desc, xmltext)
                     VALUES (load_stage_error_seq.nextval, SYSDATE, l_sqlerrm , i_xml);
                COMMIT;                   
           END;          
      END loop;

  END load_xml;

My problem is, currently I am logging the complete xml (i_xml) to the table errored_xml if any exception raises. Is there any way to log the perticular XML row attributes which throws error?

My table structure is as below,

CREATE TABLE xml_stage(
     pid NUMBER(10),
     longitude NUMBER (13,10) , 
     latitude NUMBER (13,10) , 
     date_time VARCHAR2(30),
     CONSTRAINT xml_stage_pk PRIMARY KEY (pid,date_time)
     );

CREATE TABLE errored_xml(
     error_id NUMBER(6),
     date_time DATE,
     error_desc VARCHAR2(200),
     xmltext CLOB,
     CONSTRAINT errored_xml_pk PRIMARY KEY (error_id)
     );

Thanks in advance.

Answer 1

You can insert into the errored_xml like

INSERT INTO errored_xml
     (
          error_id,
          date_time,
          error_desc,
          XMLTEXT
     )
     VALUES
     (
          load_stage_error_seq.nextval,
          SYSDATE,
          l_sqlerrm ,
          XMLELEMENT ("ROW", XMLELEMENT("pid", rec.pid) , XMLELEMENT("lon", rec.lon), XMLELEMENT("lat", rec.lat), XMLELEMENT("dt", rec.dt))
     );

(you need an alter table for varchar2 to store this xml)