DECLARE
p_max_size NUMBER := dbms_lob.lobmaxsize;
src_offset NUMBER := 1;
dst_offset NUMBER := 1;
lang_ctx NUMBER := nls_charset_id('UTF8');
default_csid CONSTANT INTEGER := nls_charset_id('ZHS16GBK');
warning NUMBER;
l_file_number PLS_INTEGER := 0;
l_count NUMBER;
l_bfile BFILE;
l_clob CLOB;
l_commitelement xmldom.domelement;
l_parser dbms_xmlparser.parser;
l_doc dbms_xmldom.domdocument;
l_nl dbms_xmldom.domnodelist;
l_n dbms_xmldom.domnode;
rootnode dbms_xmldom.domnode;
parent_rootnode dbms_xmldom.domnode;
file_length NUMBER;
block_size BINARY_INTEGER;
l_rootnode_name VARCHAR2(200);
l_status VARCHAR2(1000);
l_recerrcode VARCHAR2(1000);
l_failcount VARCHAR2(200);
l_reccount VARCHAR2(200);
l_name VARCHAR2(1000);
l_comments VARCHAR2(2000);
l_exists BOOLEAN;
FUNCTION convertclobtoxmlelement(p_document IN CLOB)
RETURN xmldom.domelement IS
x_commitelement xmldom.domelement;
l_parser xmlparser.parser;
BEGIN
l_parser := xmlparser.newparser;
xmlparser.parseclob(l_parser, p_document);
x_commitelement := xmldom.getdocumentelement(xmlparser.getdocument(l_parser));
RETURN x_commitelement;
END convertclobtoxmlelement;
BEGIN
-- 检查XML是否在路径FTP_XXX下是否存在
utl_file.fgetattr('FTP_XXX',
'simanhe_test.xml',
l_exists,
file_length,
block_size);
IF NOT l_exists THEN
dbms_output.put_line('XML文件不存在');
RETURN;
END IF;
l_bfile := bfilename('FTP_XXX', 'simanhe_test.xml');
-- 创建一个Clob
dbms_lob.createtemporary(l_clob, TRUE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
-- 将XML文件上载并转换为Clob类型
dbms_lob.loadclobfromfile(l_clob,
l_bfile,
p_max_size,
dst_offset,
src_offset,
default_csid, -- UTF8
lang_ctx, -- GBK
warning);
l_file_number := dbms_lob.fileexists(l_bfile);
IF l_file_number = 0 THEN
dbms_output.put_line('XML文件未被转换成功');
RETURN;
END IF;
dbms_lob.close(l_bfile);
-- Create a parser.
l_parser := dbms_xmlparser.newparser;
BEGIN
-- Parse the document and create a new DOM document.
dbms_xmlparser.parseclob(l_parser, l_clob);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('XML文件不完整');
RETURN;
END;
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);
-- 得到根节点
rootnode := xmldom.makenode(xmldom.getdocumentelement(xmlparser.getdocument(l_parser)));
l_rootnode_name := xmldom.getnodename(rootnode);
dbms_output.put_line('XML文件当前的节点名称为 ' || l_rootnode_name);
-- 得到根节点元素的值
dbms_xslprocessor.valueof(rootnode, 'RecCount/text()', l_reccount);
dbms_xslprocessor.valueof(rootnode, 'FailCount/text()', l_failcount);
dbms_output.put_line('XML文件当前的节点名称为 ' || l_rootnode_name ||
'的要素RecCount,FailCount值为' || l_reccount || ',' ||
l_failcount);
-- 得到根节点Dfile的属性Status的值
l_status := xmldom.getattribute(xmldom.makeelement(rootnode), 'Status');
dbms_output.put_line('XML文件当前的节点名称为 ' || l_rootnode_name ||
'的属性Status的值为' || l_status);
/*取节点Item下各元素的值,先将Items节点全部存放在 l_nl中 */
l_nl := dbms_xmldom.getelementsbytagname(l_doc, 'Item');
l_count := dbms_xmldom.getlength(l_nl);
FOR cur_emp IN 0 .. dbms_xmldom.getlength(l_nl) - 1 LOOP
l_n := dbms_xmldom.item(l_nl, cur_emp);
-- 得到节点Item下元素的值
dbms_xslprocessor.valueof(l_n, 'Name/text()', l_name);
dbms_xslprocessor.valueof(l_n, 'Comment/text()', l_comments);
-- 得到节点Item的父节点FItem
parent_rootnode := dbms_xmldom.getparentnode(l_n);
l_rootnode_name := xmldom.getnodename(parent_rootnode);
-- 得到节点FItem的属性RecErrCode的值
l_recerrcode := xmldom.getattribute(xmldom.makeelement(parent_rootnode),
'RecErrCode');
dbms_output.put_line('Name :' || l_name || ' ,Comment = ' ||
l_comments || ' ,RecErrCode = ' || l_recerrcode);
END LOOP;
-- 释放分析函数的资源
dbms_xmlparser.freeparser(l_parser);
-- 将DOC清空，释放资源
dbms_xmldom.freedocument(l_doc);
/* utl_file.frename('FTP_XXX',
'simanhe_test.xml',
'FTP_XXX',
'D_simanhe_test.xml',
FALSE);*/ -- XML文件解析完成后重命名
/*utl_file.fremove('FTP_XXX', 'simanhe_test.xml'); */ -- -- XML文件解析完成后删除文件
EXCEPTION
WHEN OTHERS THEN
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeparser(l_parser);
dbms_xmldom.freedocument(l_doc);
END;