Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: how to insert xml file into oracle database

    Hi ,

    I have a requirement to insert xml data into one of the database tables.

    for this i have used merge statement.

    create table emp3
    as
    select *From emp
    where 1=1;
    alter table emp3
    add (fax_response varchar2(50));

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

    /

    version i am using is
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0


    By executing above procedure i am getting below error

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

    could you please anyone help me to resolve the issue.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    some or many folks store XML data in CLOB column.

    what is the actual data source of the XML data?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2013
    Posts
    4
    i am loading xml file as in mode parameter .

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    https://community.oracle.com/message/4394585#4394585


    SQL*Loader can load XML file into Oracle database
    Last edited by anacedent; 01-25-14 at 17:33.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2013
    Posts
    4
    Hi I have done in the below way

    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;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •