xml导入数据库

/*** CREATE TEMP TABLE ***/
CREATE TABLE REF_CITY_RECORD(
ACTION VARCHAR2(1),
CODE_AREA VARCHAR2(100),
CODE_LOC  VARCHAR2(50),
CODE_CITY VARCHAR2(100),
CITY_DESC VARCHAR2(100),
CITY_DESC_BIG5 VARCHAR2(100),
STATUS VARCHAR2(10));

CREATE TABLE REF_ANNUAL_TURNOVER_RECORD(
ACTION VARCHAR2(1),
CODE_ANNUAL_TURNOVER VARCHAR2(10),
ANNUAL_TURNOVER_DESC VARCHAR2(50),
ANNUAL_TURNOVER_DESC_BIG5 VARCHAR2(100),
STATUS VARCHAR2(100));
/**** END ****/
  
 DECLARE
   QRYCTX DBMS_XMLGEN.CTXHANDLE;
   dst_loc CLOB;
   SAVCTX DBMS_XMLSAVE.CTXTYPE;
   N      NUMBER;
   src_loc    bfile := bfilename('UTL_FILE_DIR', '20080421170247_000004.xml');
-- DATES_01_REFEREENCE_20080421170247_000004.XML SHOULD BE PLACE UTL_FILE_DIRECTORY 
   amt        number := dbms_lob.lobmaxsize;
   src_offset number := 1;
   dst_offset number := 1;
   lang_ctx   number := dbms_lob.default_lang_ctx;
   warning    number;
   v_exists   PLS_INTEGER := 0;
  --v_rowtag VARCHAR2(30) := 'REF_CITY_RECORD';

 BEGIN
   DBMS_LOB.CREATETEMPORARY(dst_loc, true);
   DBMS_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE ------------');
   dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
   v_exists := DBMS_LOB.fileexists(src_loc);
   IF v_exists = 0 THEN
     dbms_output.put_line('FILE NOT EXIST');
   ELSE
     dbms_output.put_line('FILE AND DIR VALID');
   END IF;
   dbms_lob.LOADCLOBFROMFILE(dst_loc,
                             src_loc,
                             amt,
                             dst_offset,
                             src_offset,
                             dbms_lob.default_csid,
                             lang_ctx,
                             warning);
   dbms_output.put_line(' Amount specified ' || amt);
   dbms_output.put_line(' Number of bytes read from source: ' ||
                        (src_offset - 1));
   dbms_output.put_line(' Number of characters written to destination: ' ||
                        (dst_offset - 1));
   if (warning = dbms_lob.warn_inconvertible_char) then
     dbms_output.put_line('Warning: Inconvertible character');
   end if;
   dbms_lob.filecloseall();
 --  DBMS_OUTPUT.PUT_LINE(dst_loc);
    SAVCTX := DBMS_XMLSAVE.NEWCONTEXT('REF_CITY_RECORD');
   DBMS_XMLSAVE.CLEARUPDATECOLUMNLIST(SAVCTX);
   DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ACTION');
   DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_AREA');
  DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_LOC');
  DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_CITY');
  DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CITY_DESC');
  DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CITY_DESC_BIG5');
  DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'STATUS');
  DBMS_XMLSAVE.setRowTag(SAVCTX, 'REF_CITY');
  --DBMS_XMLSave.setDateFormat(SAVCTX,   'dd/MM/yyyy HH24:mm:ss'); 
   N := DBMS_XMLSAVE.INSERTXML(SAVCTX, dst_loc);
  DBMS_OUTPUT.PUT_LINE('REF_CITY_RECORD RECORD: ' ||N);
 
   SAVCTX := DBMS_XMLSAVE.NEWCONTEXT('REF_ANNUAL_TURNOVER_RECORD');
   DBMS_XMLSAVE.CLEARUPDATECOLUMNLIST(SAVCTX);
   DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ACTION');
   DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_ANNUAL_TURNOVER');
   DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ANNUAL_TURNOVER_DESC');
   DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ANNUAL_TURNOVER_DESC_BIG5');
   DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'STATUS');
  DBMS_XMLSAVE.setRowTag(SAVCTX, 'REF_ANNUAL_TURNOVER');
   N := DBMS_XMLSAVE.INSERTXML(SAVCTX, dst_loc);
  DBMS_OUTPUT.PUT_LINE('REF_ANNUAL_TURNOVER_RECORD RECORD : ' ||N);
 
   DBMS_XMLSAVE.CLOSECONTEXT(SAVCTX);
COMMIT;
exception when others then
 rollback;
 dbms_output.put_line(substr(sqlerrm,1,200));
 END;
/