Common scripts used by oracle to parse xml strings
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