DECLARE

--xml字符串

v_query VARCHAR2(2000) := '-1SELECT TEST_TB_HXQ.ID FROM TEST_TB_HXQ,USERS a2,C_STOREGRADE a1,TEST_DET a0 WHERE ( (TEST_TB_HXQ.AD_CLIENT_ID=37) ) AND (a0.ID (+)=TEST_TB_HXQ.DBUSER) AND (a1.ID (+)=TEST_TB_HXQ.C_STOREGRADE_ID) AND (a2.ID (+)=TEST_TB_HXQ.MODIFIERID)

--定义记录xml变量

TYPE xml_record_type IS RECORD(

id     NUMBER(10),

iquery VARCHAR2(2000),

itable NUMBER(10));

TYPE xml_table_type IS TABLE OF xml_record_type;

xml_table xml_table_type;

--xml类型

v_xml xmltype;

--批量接受secction

TYPE selection_table_type IS TABLE OF VARCHAR2(100);

selection_table selection_table_type;

BEGIN

v_xml := xmltype(v_query);

dbms_output.put_line(CHR(13)||'---------------第一种xmltable解析xml方法---------------');

--查询xml内容

SELECT * BULK COLLECT

INTO xml_table

FROM xmltable('/data' passing v_xml columns id NUMBER(10) path 'id',

iquery VARCHAR2(2000) path 'query',

itable NUMBER(10) path 'table');

--遍历xml数据

FOR temp IN xml_table.first .. xml_table.last

LOOP

dbms_output.put_line('>>    id: '||xml_table(temp).id || '   >> itable: '||xml_table(temp).itable);

dbms_output.put_line('>>   iquery  '||xml_table(temp).iquery );

END LOOP;

dbms_output.put_line(CHR(13)||CHR(13)||'---------------第二种XMLSEQUENCE解析xml方法---------------'||CHR(13));

--获取重复的selection标签

SELECT extractvalue(VALUE(t), '/selection') BULK COLLECT

INTO selection_table

FROM TABLE(XMLSEQUENCE(EXTRACT(v_xml, '/data/selection'))) t;

--遍历xml数据

FOR temp IN selection_table.first .. selection_table.last

LOOP

dbms_output.put_line('>>   selection  '||selection_table(temp));

END LOOP;

END;