Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (
  xml CLOB,
  json CLOB CHECK ( json IS JSON )
);
INSERT INTO table_name ( xml, json ) VALUES (
  '<a><b>BBB</b><c>CCC</c><d>DDD</d></a>',
  '{"a":"aaa","b":"bbb","c":[1,2,3,4],"d":"ddd"}'
);
1 rows affected

SELECT XMLQUERY(
          '*/c/text()'
          PASSING XMLTYPE(xml)
          RETURNING CONTENT
       ) AS c_xml_value,
       JSON_QUERY(
         json,
         '$.c'
         RETURNING VARCHAR2(50)
       ) AS c_json_value
FROM   table_name


C_XML_VALUE
C_JSON_VALUE


CCC
[1,2,3,4]


DROP TABLE table_name;
CREATE TABLE table_name ( value CLOB );
INSERT INTO table_name ( value )
SELECT '<a><b>BBB</b><c>CCC</c><d>DDD</d></a>' FROM DUAL UNION ALL
SELECT '{"a":"aaa","b":"bbb","c":[1,2,3,4],"d":"ddd"}' FROM DUAL;
2 rows affected

SELECT CASE
       WHEN value LIKE '<%'
       THEN CAST(
              XMLQUERY( '*/c/text()' PASSING XMLTYPE(value) RETURNING CONTENT )
              AS VARCHAR2(50)
            )
       ELSE JSON_QUERY( value, '$.c' RETURNING VARCHAR2(50) )
       END AS c_value
FROM   table_name


C_VALUE


CCC

[1,2,3,4]