loading xml file to oracle table

Question

I have a requirement to load an xml file to a particular table. i have tried with below code but it did not solve my problem.

The xml data is:

<FAX>  
  <EMAILOG>  
    <ID>7839</ID>  
    <RESPONSE>FAX SENT</RESPONSE>  
  </EMAILOG>  
  <EMAILOG>  
    <ID>7566</ID>  
    <RESPONSE>FAX NOT SENT</RESPONSE>  
  </EMAILOG>  
</FAX>  

I was executing procedure with a help of pl/sql developer grid. right clicking the procedure Test --> Loading xml file from harddisk --> execute.

Here is the DDL explaining the procedure.

create table emp3 as select * From emp  where 1=1;    
alter table emp3 add (fax_response varchar2(50)); 
create global temporary table tmp_xml of xmltype xmltype store as securefile binary xml;

create or replace procedure proc_upd_email_records ( loc_xml in clob ) is
begin
  insert into tmp_xml
  values (xmlparse(document loc_xml)) ;

  merge into emp3 e
  using (
    select id
         , response 
    from tmp_xml t
       , xmltable(
           '/FAX/EMAILOG'
           passing t.object_value 
           columns id       number        path 'ID'
                 , response varchar2(250) path 'RESPONSE' 
         )
  ) v
  on ( e.empno = v.id )
  when matched then update
    set e.fax_response = v.response;
end;
/

by executing via the PL/SQL Developer "Test" procedure method I am getting below error

ora 22805 : can not insert null object into object tables or nested tables

Answer 1

This works just fine for me (using Oracle Database 11g Express Edition Release 11.2.0.2.0).

declare 
   v_xml clob;
begin
   v_xml := 
'<FAX>  
   <EMAILOG>  
      <ID>7839</ID>  
      <RESPONSE>FAX SENT</RESPONSE>  
   </EMAILOG>  
   <EMAILOG>  
      <ID>7566</ID>  
      <RESPONSE>FAX NOT SENT</RESPONSE>  
   </EMAILOG>  
</FAX>';

  proc_upd_email_records(v_xml);
end;