Get the App
SLTechnology News&Howtos  ›  Database  › 

Common scripts used by oracle to parse xml strings

Shulou Source: shulou.com Published: 2022-06-01 05:29:34 11月17日 Update

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

Tags: Messages characters strings normal fields arrays types 2D output commonly used scripts Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Shulou Tech Info vpn macOS Redmi Xiaomi