6 Replies Latest reply: Jan 24, 2014 10:01 AM by mbb774 RSS

Updating a table column Using xml data

mbb774 Newbie
Currently Being Moderated

Hi,

 

I have a requirement to update a particular table column Using xml data. for this i have written below code but i am not able to insert . could you please any one look into this .

 

 

create table emp3
as
select *From emp
where 1=1;
alter table emp3
add (fax_response varchar2(50));
/*create sequence EmailRecords_XMLFILE_SEQ
  minvalue 1
  maxvalue 999999999999999999999999999
  start with 1
  increment by 1
  nocache;*/
/* create global temporary table EmailRecords_XMLFILE
  (
  ID NUMBER not null,
  xmlfile CLOB
  )
  on commit preserve rows;*/
/* create global temporary table UPD_Email_Records_With_Xml
  (
  id NUMBER not null,
  response VARCHAR2(500)
  )
  on commit preserve rows; */

 

 

xml data is

 

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

 

CREATE OR REPLACE PROCEDURE proc_upd_email_records (
   loc_xml          IN       CLOB,
   p_err_code_out   OUT      NUMBER,
   p_err_mesg_out   OUT      VARCHAR2
)
IS
   loc_id   NUMBER;
BEGIN
   loc_id := emailrecords_xmlfile_seq.NEXTVAL; --created sequence
   INSERT INTO emailrecords_xmlfile --created Global Temp table
               (ID, xmlfile
               )
        VALUES (loc_id, loc_xml
               );
   COMMIT;
      insert into UPD_Email_Records_With_Xml --created Global Temp table
        (ID, RESPONSE)
        select x1.id,
                  x1.RESPONSE
          from EmailRecords_XMLFILE,
               xmltable('/FAX/EMAILOGID' passing
                        xmltype.createxml(EmailRecords_XMLFILE.xmlfile)
                        columns header_no for ordinality,
                        id number path 'ID',
                        RESPONSE VARCHAR2(250) path 'RESPONSE'
                           ) x1
         where EmailRecords_XMLFILE.id = loc_id;
   COMMIT;
   UPDATE emp3 er
      SET er.fax_response = (SELECT response
                           FROM upd_email_records_with_xml pr
                          WHERE pr.ID = er.empno)
    WHERE er.empno IN (SELECT ID
                         FROM upd_email_records_with_xml);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      raise_application_error
         (-20000,
          'Sorry ! The Xml File which is passed is empty. Please try with Valid Xml File. Thank you!!! '
         );
   WHEN OTHERS
   THEN
      p_err_code_out := 4;
      p_err_mesg_out := 'error in insertion=> ' || SQLERRM;
END proc_upd_email_records;
{code}{code}

 

Any one suggest me a bit easier way to insert data...

 

Thank you....

  • 1. Re: Updating a table column Using xml data
    odie_63 Guru
    Currently Being Moderated

    You're complicating things

     

    A simple MERGE statement will do.

    create or replace procedure proc_upd_email_records ( 

    loc_xml in clob

    is

    begin

     

      merge into emp3 e

      using (

        select id

             , response 

        from xmltable(

               '/FAX/EMAILOG'

               passing xmlparse(document loc_xml) 

               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;

    /

     

    There's no added value in using those temp tables if you're not at least using a staging XMLType column (preferably binary XML storage).

    - How large is the input XML?

    - What's the db version?

  • 2. Re: Updating a table column Using xml data
    mbb774 Newbie
    Currently Being Moderated

    Hi Odie,

     

    Thank you very much...

     

    i  am loading  150 kb xml file.

     

    and version running on my machine is


    select * from v$version
    where banner like 'Oracle%'



    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production


     

  • 3. Re: Updating a table column Using xml data
    mbb774 Newbie
    Currently Being Moderated

    Hi Odie,

     

    Records are not inserted in the table.

     

    could you please suggest me  and i am passing 2 mb xml file.

  • 4. Re: Updating a table column Using xml data
    odie_63 Guru
    Currently Being Moderated

    Records are not inserted in the table.

    Care to explain a bit more ?

     

    Are you getting an error?

    Does it take too much time to execute?

     

    When the input XML document gets to that size, it's interesting to first store it a binary XMLType column and parse it from there.

     

    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;

    /

  • 5. Re: Updating a table column Using xml data
    mbb774 Newbie
    Currently Being Moderated

    Hi Odie,

     

    Thanks for the reply.

     

    i am not getting any error for the previous code but the records are not inserting into the target table (emp3),it is taking less than 1 min

     

    for the present code which you have provided , i am getting below error

     

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

     

    i am unable to fix the above error,

     

    could you please suggest me

  • 6. Re: Updating a table column Using xml data
    mbb774 Newbie
    Currently Being Moderated

    Hi Odie,

     

    I have tried several ways fix the above issue. could you please suggest me to resolve the above error. i even gone through all your previous posts  , but no luck...........

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points