Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE table_name (xml_data) AS 
SELECT '<elementA>
  <firstName>snoopy</firstName>
  <lastName>brown</lastName>
  <favoriteNumbers>
    <value>1</value>
    <value>2</value>
    <value>3</value>
  </favoriteNumbers>
</elementA>' FROM DUAL;
1 rows affected

SELECT JSON_OBJECTAGG( id VALUE text ) AS json
FROM   table_name t
       CROSS APPLY XMLTABLE(
         '/elementA/*'
         PASSING XMLTYPE( t.xml_data )
         COLUMNS id   VARCHAR2(200) PATH './name()',
                 text VARCHAR2(200) PATH './text()'
       );


JSON


{"firstName":"snoopy","lastName":"brown","favoriteNumbers":null}


SELECT x2j.json
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT JSON_OBJECT(
                  KEY 'firstName'       VALUE x.firstName,
                  KEY 'lastName'        VALUE x.lastName,
                  KEY 'favoriteNumbers' VALUE JSON_OBJECT(
                      KEY 'value' VALUE f.favoriteNumbers
                    )
                ) AS json
         FROM   XMLTABLE(
                  '/elementA'
                  PASSING XMLTYPE(t.xml_data)
                  COLUMNS
                    firstName VARCHAR2(200) PATH './firstName',
                    lastName  VARCHAR2(200) PATH './lastName',
                    favoriteNumbers XMLTYPE PATH './favoriteNumbers'
                ) x
                CROSS JOIN LATERAL (
                  SELECT JSON_ARRAYAGG(value) AS favoriteNumbers
                  FROM   XMLTABLE(
                           '/favoriteNumbers/value'
                           PASSING x.favoriteNumbers
                           COLUMNS
                             value NUMBER PATH '.'
                         )
                ) f
       ) x2j


JSON


{"firstName":"snoopy","lastName":"brown","favoriteNumbers":{"value":[1,2,3]}}


SELECT x2j.json FROM table_name t CROSS JOIN LATERAL (SELECT JSON_OBJECT(KEY 'firstName' VALUE x.firstName, KEY 'lastName' VALUE x.lastName, KEY 'favoriteNumbers' VALUE JSON_OBJECT( KEY 'value' VALUE f.favoriteNumbers ) ) AS json FROM XMLTABLE( '/elementA' PASSING XMLTYPE(t.xml_data) COLUMNS firstName VARCHAR2(200) PATH './firstName', lastName VARCHAR2(200) PATH './lastName', favoriteNumbers XMLTYPE PATH './favoriteNumbers' ) x CROSS JOIN LATERAL ( SELECT JSON_ARRAYAGG(value) AS favoriteNumbers FROM XMLTABLE( '/favoriteNumbers/value' PASSING x.favoriteNumbers COLUMNS value NUMBER PATH '.' ) ) f ) x2j


JSON


{"firstName":"snoopy","lastName":"brown","favoriteNumbers":{"value":[1,2,3]}}