Shulou(Shulou.com)06/01 Report--

DECLARE

L_operator VARCHAR2 (100)

L_year VARCHAR2 (10)

L_month VARCHAR2 (10)

L_xml_str VARCHAR2 (4000);-- xml string message

L_xmltype xmltype

BEGIN

-- A given XML message string

L_xml_str: ='

SMILE

2019

twelve

one

CODE1

NAME1

two

CODE2

NAME2

three

CODE3

NAME3

'

Convert XML message to xmltype type for subsequent parsing

L_xmltype: = xmltype.createxml (l_xml_str)

-- ordinary and simple parsing field values

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) path 'OPERATOR'

, YEAR VARCHAR2 (500) path 'YEAR'

, MONTH VARCHAR2, path 'MONTH') AS xmlt

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

-- traverse the output two-dimensional array

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) path 'ID'

, code VARCHAR2 (500) path 'CODE'

, NAME VARCHAR2, 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.