DECLARE

l_operator VARCHAR2(100);

l_year VARCHAR2(10);

l_month VARCHAR2(10);

l_xml_str VARCHAR2(4000); --xml字符串报文

l_xmltype xmltype;

BEGIN

--给定XML报文串

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

<XMLMSG>

<INFO>

<OPERATOR>SMILE</OPERATOR>

<YEAR>2019</YEAR>

<MONTH>12</MONTH>

</INFO>

<STRARRAYS>

<STRARRAY>

<ID>1</ID>

<CODE>CODE1</CODE>

<NAME>NAME1</NAME>

</STRARRAY>

<STRARRAY>

<ID>2</ID>

<CODE>CODE2</CODE>

<NAME>NAME2</NAME>

</STRARRAY>

<STRARRAY>

<ID>3</ID>

<CODE>CODE3</CODE>

<NAME>NAME3</NAME>

</STRARRAY>

</STRARRAYS>

</XMLMSG>';

--将XML报文转换成xmltype类型，便于后续解析

l_xmltype := xmltype.createxml(l_xml_str);

--普通简单的解析字段值

SELECT xmlt.operator

,xmlt.year

,xmlt.month

INTO l_operator

,l_year

,l_month

FROM xmltable('$root/XMLMSG/INFO' passing l_xmltype AS "root" columns operator VARCHAR2(500) path 'OPERATOR'

,YEAR VARCHAR2(500) path 'YEAR'

,MONTH VARCHAR2(500) path 'MONTH') AS xmlt;

dbms_output.put_line('operator:' || l_operator || '--year:' || l_year || '--month:' || l_month);

--遍历输出二维数组

FOR rec_list IN (SELECT xmlt.id

,xmlt.code

,xmlt.name

INTO l_operator

,l_year

,l_month

FROM xmltable('$root/XMLMSG/STRARRAYS/STRARRAY' passing l_xmltype AS "root" columns id VARCHAR2(500) path 'ID'

,code VARCHAR2(500) path 'CODE'

,NAME VARCHAR2(500) path 'NAME') AS xmlt) LOOP

dbms_output.put_line(rec_list.id || '--' || rec_list.code || '--' || rec_list.name);

END LOOP;

EXCEPTION

WHEN fnd_api.g_exc_error THEN

dbms_output.put_line('error:' || SQLCODE || '--' || SQLERRM);

WHEN fnd_api.g_exc_unexpected_error THEN

dbms_output.put_line('Unexpected_error.' || SQLCODE || '--' || SQLERRM);

WHEN OTHERS THEN

dbms_output.put_line('Other error:' || SQLCODE || '--' || SQLERRM);

END;