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;  

备注：PLSQL解析xml精确定位具体节点。