DECLARE

    v_xmlclob    CLOB := '<?xml version="1.0" encoding="UTF-8"?>

    <header ID="1" ADDRESS="BEIJING">

    <header_name>test</header_name>

    <line>

    <line_num>1</line_num>

    <name>TEST1</name>

    <sex>男</sex>

    <detail>

    <age>12</age>

    </detail>

    <detail>

    <age>13</age>

    </detail>

    </line>

    <line>

    <line_num>2</line_num>

    <name>TEST2</name>

    <sex>女</sex>

    <detail>

    <age>14</age>

    </detail>

    <detail>

    <age>15</age>

    </detail>

    </line>

    </header>

    ';

      v_clob       CLOB;

      v_xml_str    sys.xmltype;

      v_resultcode VARCHAR2(200);  

      v_parser      dbms_xmlparser.parser;

      v_doc         dbms_xmldom.domdocument;

      v_rootnode    dbms_xmldom.domnode;

      v_linelist    dbms_xmldom.domnodelist;

      v_detailnodes dbms_xmldom.domnode;

      v_detaillist  dbms_xmldom.domnodelist;

      v_detailnode  dbms_xmldom.domnode;

      v_detail_num  NUMBER;

      v_linenode    dbms_xmldom.domnode;

      v_count       NUMBER;

      v_line_num    VARCHAR2(100);

      v_name        VARCHAR2(100);

      v_sex         VARCHAR2(100);

      v_age         VARCHAR2(100);

      rootname      VARCHAR2(100);

      v_header_name VARCHAR2(100);

    BEGIN

      --1.创建CLOB

      dbms_lob.createtemporary(v_clob, TRUE);

      v_clob := v_xmlclob;

      --2.创建解析器，加载v_clob

      v_parser := dbms_xmlparser.newparser;

      dbms_xmlparser.parseclob(v_parser, v_clob);

      dbms_lob.freetemporary(v_clob); --释放clob

      --3.得到根节点

      v_rootnode := xmldom.makenode(xmldom.getdocumentelement(xmlparser.getdocument(v_parser)));

      rootname   := xmldom.getnodename(v_rootnode);

      dbms_output.put_line(rootname);

      --4.获取根节点中的值

      dbms_xslprocessor.valueof(v_rootnode,

                                'header_name/text()',

                                v_header_name);  

      dbms_output.put_line(v_header_name);

      --5.获取根节点中的属性值

      v_resultcode := xmldom.getattribute(xmldom.makeelement(v_rootnode),

                                          'ADDRESS');

      dbms_output.put_line(v_resultcode);

      --6.创建doc

      v_doc := dbms_xmlparser.getdocument(v_parser);

      dbms_xmlparser.freeparser(v_parser); --释放解析器

      v_linelist := dbms_xmldom.getelementsbytagname(v_doc, 'line'); --获取line节点

      v_count    := dbms_xmldom.getlength(v_linelist);

      dbms_output.put_line('v_count:' || v_count);

      FOR cur_emp IN 0 .. v_count - 1 LOOP

        v_linenode := dbms_xmldom.item(v_linelist, cur_emp); --获取节点

        dbms_xslprocessor.valueof(v_linenode, 'line_num/text()', v_line_num); --得到节点下元素的值

        dbms_output.put_line('v_line_num' || v_line_num);

        v_detaillist := dbms_xslprocessor.selectnodes(v_linenode, 'detail'); --获取该节点下的子节点detail

        v_detail_num := dbms_xmldom.getlength(v_detaillist);

        dbms_output.put_line('v_detail_num:' || v_detail_num);

        FOR cur_emp2 IN 0 .. v_detail_num - 1 LOOP

          v_detailnode := dbms_xmldom.item(v_detaillist, cur_emp2);

          dbms_xslprocessor.valueof(v_detailnode, 'age/text()', v_age); --行号

          dbms_output.put_line('age' || v_age);

        END LOOP;

      END LOOP;

      xmldom.freedocument(v_doc); --释放doc资源

    EXCEPTION

      WHEN OTHERS THEN

        dbms_lob.freetemporary(v_clob);

        dbms_xmlparser.freeparser(v_parser);

        dbms_xmldom.freedocument(v_doc);  

    END;