plsql解析XML

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;