oracle 导入xml文件怎么打开,xml文件导入oracle

针对xml文件导入oracle做了实验测试，数据库为oracle11g，步骤如下：

1、创建测试xml文件test.XML

100

aaa

101

bbb

102

ccc222

2、创建测试表

create table TEST

(

USER_ID NUMBER(10),

USER_NAME VARCHAR2(30)

);

3、创建路径 XML_DIR

create directory XML_DIR as 'F:\xmltest';

4、执行过程体

DECLARE

QRYCTX DBMS_XMLGEN.CTXHANDLE;

dst_loc CLOB;

SAVCTX DBMS_XMLSTORE.CTXTYPE;

N      NUMBER;

src_loc    bfile := bfilename('XML_DIR', TEST.XML');

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;

BEGIN

DBMS_LOB.CREATETEMPORARY(dst_loc, true);

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;

----将数据从OS文件加载到CLOB变量-------

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_XMLSTORE.NEWCONTEXT('TEST');

DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(SAVCTX);

DBMS_XMLSTORE.SETUPDATECOLUMN(SAVCTX, 'USER_ID');

DBMS_XMLSTORE.SETUPDATECOLUMN(SAVCTX, 'USER_NAME');

N := DBMS_XMLSTORE.INSERTXML(SAVCTX, dst_loc);

DBMS_XMLSTORE.CLOSECONTEXT(SAVCTX);

END;

/

commit;

5、查看结果

select * from TEST t