XML Reading in Oracle

@id

Hi All,

I have a requirement to read xml data and store in oracle staging table. I used the following code to read xml data and insert into table.

declare

  l_input_xml xmltype := xmltype(bfilename('TEST_DIR', 'test.xml'), nls_charset_id('AL32UTF8'));

begin                            

  insert into emp(empno, ename, job)

    select x.*

    from   xmltable(

                   '/employees/employee'                            

                    passing l_input_xml

                    columns empno number          path '@id'

                               , ename varchar2(100)  path 'name'

                               , job   varchar2(100)     path 'job'

                   ) x;

exception

  when others then

    dbms_output.put_line('Error: ' || SQLERRM);

end;

I have created the directory by using the following code: