PLSQL_PLSQL读和写XML文件方式（案例）

2012-05-01 Created By BaoXinjian

一、写XML文件

---

Step1. 创建测试目录

--创建测试目录
CREATE OR REPLACE DIRECTORY bxjxml AS '/home/oracle/bxjxml'; 

--目录权限分配
GRANT READ, WRITE ON DIRECTORY bxjxml TO public;

 

Step2. 写文件

DECLARE
   f_emp        UTL_FILE.FILE_TYPE;
   v_sql        VARCHAR2 (1000);
   myclob       CLOB;
BEGIN
   v_sql := 'SELECT   mgr.first_name manager,
                      emp.first_name || emp.last_name empname,
                      emp.email
               FROM   hr.employees emp, hr.employees mgr
              WHERE   emp.manager_id = mgr.employee_id
                AND   mgr.first_name IN ('''|| 'Alberto'|| ''','''|| 'Gerald'|| ''')';
   DBMS_OUTPUT.put_line (v_sql);

   SELECT   DBMS_XMLGEN.GETXML (v_sql) INTO myclob FROM DUAL;

   f_emp := UTL_FILE.FOPEN ('BXJXML','BXJXML.XML','W',32767);
   UTL_FILE.PUT (f_emp, myclob);
   UTL_FILE.FCLOSE (f_emp);
END;
/

Step3. 查看输出结果

 

二、读XML文件

---

Step.1 创建读取XML文件的Package

CREATE OR REPLACE PROCEDURE parse_xml_file
IS
   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
   UTL_FILE.fgetattr ('BXJXML','bxjxml.xml',l_exists,file_length,block_size);

   IF NOT l_exists
   THEN
      DBMS_OUTPUT.put_line ('XML File not exist!!!');
      RETURN;
   END IF;

   l_bfile := BFILENAME ('BXJXML', 'bxjxml.xml');
   DBMS_LOB.createtemporary (l_clob, TRUE);
   DBMS_LOB.open (l_bfile, DBMS_LOB.lob_readonly);
   DBMS_LOB.loadclobfromfile (l_clob, l_bfile,  p_max_size, dst_offset, src_offset, default_csid, lang_ctx, warning);
   l_file_number := DBMS_LOB.fileexists (l_bfile);

   IF l_file_number = 0
   THEN
      DBMS_OUTPUT.put_line ('XML File Convert Failed!!!');
      RETURN;
   END IF;

   DBMS_LOB.close (l_bfile);
   l_parser := DBMS_XMLPARSER.newParser;

   BEGIN
      DBMS_XMLPARSER.parseClob (l_parser, l_clob);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_Line ('XML File Not Full!!!');
         RETURN;
   END;

   l_doc := DBMS_XMLPARSER.getDocument (l_parser);
   DBMS_LOB.freetemporary (l_clob);
   rootnode := xmldom.makeNode ( xmldom.getDocumentElement (xmlparser.getDocument (l_parser)) );
   l_rootnode_name := xmldom.getNodeName (rootnode);
   DBMS_OUTPUT.put_line ('The root node name of the XML File is :' || l_rootnode_name);
   DBMS_XSLPROCESSOR.valueOf (rootnode, 'RecCount/text()', l_reccount);
   DBMS_XSLPROCESSOR.valueOf (rootnode, 'FailCount/text()', l_Failcount);
   DBMS_OUTPUT.put_line(   'The name of the Current Node in The File is : ' || l_rootnode_name
                        || '''s elements RecCount,FailCount Value is :'|| l_reccount|| ','|| l_Failcount);
   l_status := xmldom.getAttribute (xmldom.makeElement (rootnode), 'Status');
   DBMS_OUTPUT.put_line(   'The name of the Current Node in The File is : '|| l_rootnode_name|| '''s elements Status Value is :'|| l_status);

   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);
      DBMS_XSLPROCESSOR.valueOf (l_n, 'Name/text()', l_name);
      DBMS_XSLPROCESSOR.valueOf (l_n, 'Comment/text()', l_comments);

      parent_rootnode := DBMS_XMLDOM.getParentNode (l_n);
      l_rootnode_name := xmldom.getNodeName (parent_rootnode);

      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);
   DBMS_XMLDOM.freeDocument (l_doc);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_LOB.freetemporary (l_clob);
      DBMS_XMLPARSER.freeParser (l_parser);
      DBMS_XMLDOM.freeDocument (l_doc);
END;

 

Step2. 测试Procedure，并确认输出结果

BEGIN
   parse_xml_file;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
END;

--The root node name of the XML File is :Dfile
--The name of the Current Node in The File is : Dfile's elements --RecCount,FailCount Value is :200,1
--The name of the Current Node in The File is : Dfile's elements Status --Value is :3
--Name :test1 ,Comment = BXJCOMMENT1, RecErrCode = 2901
--Name :test2 ,Comment = BXJCOMMENT2, RecErrCode = 2901
--Name :test3 ,Comment = BXJCOMMENT3, RecErrCode = 2902
--Name :test4 ,Comment = BXJCOMMENT4, RecErrCode = 2902

 

Thanks and Regards