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精确定位具体节点。