CREATE TABLE loadxmlfile (xmldata CLOB);
CREATE TABLE loadxml (id NUMBER, xmldata XMLTYPE);

create or replace PROCEDURE load_xmlfile
  (
     inp_filename  VARCHAR2
    ,inp_dirname   VARCHAR2 DEFAULT NULL   
  ) AS
 
     v_xlob                 CLOB;
     v_dir_not_exists       EXCEPTION;
     v_id NUMBER(5);

  BEGIN
        v_xlob := dbms_xslprocessor.read2clob('SCOTTDIR', inp_filename);
       
        INSERT INTO loadxmlfile VALUES(v_xlob);

        -- Remove non breaking space - CHR(160) with space
        UPDATE loadxmlfile SET xmldata = REPLACE(xmldata, CHR(160), ' ') ;    

        SELECT MAX(id) INTO v_id FROM loadxml;
        INSERT INTO loadxml
           (id, xmldata)
        SELECT v_id, xmltype(xmldata)  FROM loadxmlfile;
   
        COMMIT;
   
  EXCEPTION   
     WHEN v_dir_not_exists THEN
      DBMS_OUTPUT.PUT_LINE('v_dir_not_exists '||SQLERRM);
    
     WHEN utl_file.access_denied THEN
      DBMS_OUTPUT.PUT_LINE('access_denied '||SQLERRM);         
       
     WHEN utl_file.read_error THEN
      DBMS_OUTPUT.PUT_LINE('read_error '||SQLERRM);         

     WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('OTHERS '||SQLERRM);
  END load_xmlfile;

create or replace PROCEDURE load_xmlfile
  (
     inp_filename  VARCHAR2
    ,inp_dirname   VARCHAR2 DEFAULT NULL   
  ) AS
 
     v_xlob                 CLOB;
     v_dir_not_exists       EXCEPTION;
     v_id NUMBER(5);

  BEGIN
        v_xlob := dbms_xslprocessor.read2clob('SCOTTDIR', inp_filename);
       
        INSERT INTO loadxmlfile VALUES(v_xlob);

        -- Remove non breaking space - CHR(160) with space
        UPDATE loadxmlfile SET xmldata = REPLACE(xmldata, CHR(160), ' ') ;    

        SELECT MAX(id) INTO v_id FROM loadxml;
        INSERT INTO loadxml
           (id, xmldata)
        SELECT v_id, xmltype(xmldata)  FROM loadxmlfile;
   
        COMMIT;
END;