CREATE OR REPLACE
procedure xml_read_file (in_filename in varchar2)
is
 my_dir  varchar2(10) := 'XML_DIR';

  l_bfile   BFILE;
  l_clob    CLOB;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(1000);   
  v_emp_id number(10);
  v_emp_name  varchar2(50);
  v_birthday date;
src_csid       NUMBER := NLS_CHARSET_ID('UTF8');  
dest_offset    INTEGER := 1;
src_offset     INTEGER := 1;
lang_context   INTEGER := dbms_lob.default_lang_ctx;
warning        INTEGER;

BEGIN

  l_bfile := BFileName(my_dir, in_filename);
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  
  dbms_lob.loadclobfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile), dest_offset,src_offset, src_csid, lang_context, warning);                        
  
  dbms_lob.close(l_bfile);

  -- make sure implicit date conversions are performed correctly
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD/MM/RR HH24:MI:SS''');


  -- Create a parser.
  l_parser := dbms_xmlparser.newParser;

  -- Parse the document and create a new DOM document.
    dbms_xmlparser.parseClob(l_parser, l_clob); 
    l_doc := dbms_xmlparser.getDocument(l_parser);


  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);

  -- Get a list of all the  nodes in the document using the XPATH syntax.
  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/employees');

  -- Loop through the list and create a new record in a tble collection
  -- for each  record.
  FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
  
    l_n := dbms_xmldom.item(l_nl, cur_emp); 

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'emp_id/text()',v_emp_id);
    dbms_xslprocessor.valueOf(l_n,'emp_name/text()',v_emp_name); 
     dbms_xslprocessor.valueOf(l_n,'birthday/text()',v_birthday); 
    

insert into emp(emp_id , emp_name , birthday)
values(v_emp_id , v_emp_name , v_birthday)

  END LOOP;
  


  -- Free any resources associated with the document now it
  -- is no longer needed.
  dbms_xmldom.freeDocument(l_doc); 
  
  --remove file to another directory

	commit;
EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc); 
    ROLLBACK;
END;
/