oracle 解析xml字符串常用脚本

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 := '

SMILE

2019

12

1

CODE1

NAME1

2

CODE2

NAME2

3

CODE3

NAME3

';

--将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;