Oracle 18c

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';
SELECT JSON_OBJECTAGG( id VALUE text )
FROM   XMLTABLE(
         '/xml/*'
         PASSING XMLTYPE( '<xml><name>himanshu</name></xml>')
         COLUMNS id   VARCHAR2(200) PATH './name()',
                 text VARCHAR2(200) PATH './text()'
       );


JSON_OBJECTAGG(IDVALUETEXT)


{"name":"himanshu"}


CREATE TABLE table_name(xml) AS
  SELECT '<xml><name>himasnhu</name><age>24</age></xml>' FROM DUAL;
1 rows affected

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


JSON


{"name":"himasnhu","age":"24"}