你不会像python那样找到一个两行的解决方案。相反，您需要解析XML并生成JSON。为此，您可以使用XMLTABLE和JSON函数：

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

其中，对于样本数据：

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;

输出：
| JSON|
| --------------|
| {“firstName”：“snoopy”，“lastName”：“brown”，“favoriteNumbers”：{“value”：[1，2，3]}}|
已经说过，你不会像python那样得到一个2行的解决方案，你可以在Oracle中得到一个1行的解决方案：

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

但这只是与上面相同的查询，所有空格都连接在一起。代码行数不一定是衡量代码的有用指标。
fiddle

Oracle已发布APEX 22.2，它通过Oracle支持服务在Oracle数据库的所有版本（EE、SE 2、SE和SE 1）上得到完全支持，12.1.0.2或更高版本，并具有有效的Oracle数据库技术支持协议。
对于这个例子，APEX 5 installation就足够了。
这样，您就可以使用下面给出的简单过程来获取JSON对象

declare
             l_xml xmltype;
           begin
          
              select
                xmltype
            ( wrapper(
            myobj_nt(
             myobj(1,'Connor'),
                myobj(2,'McDonald')
            )
             ))
           into l_xml
           from dual;
        
           apex_json.initialize_clob_output;
           apex_json.write(l_xml);
           dbms_output.put_line(apex_json.get_clob_output);
           apex_json.free_output;
         end;

如果你想要一个替代方案，你可以使用下面的，但对于大数据集来说，它的性能会更慢。

with edge_data as (
        select x.* 
          FROM tmp_xml t
            , XMLTable(
              'declare function local:getChildren($e as node(), $pID as xs:string?) as element()*
               {
                 for $i at $p in $e/(child::*|attribute::*)
                 let $ID := if ($pID) then concat($pID,".",$p) else "1"
                return element r
                {
                   element node_id {$ID}
                 , element parent_node_id {$pID}
                 , element node_name {name($i)}
                 , if ($i instance of attribute())
                     then ( element node_value {data($i)}, element node_kind {"attribute"} )
                     else ( element node_value {$i/text()}, element node_kind {"element"} )
                 }
                 | local:getChildren($i,$ID)
               }; (: :)
               local:getChildren($d,())'
                passing t.xml_data as "d"
               columns node_id         varchar2(100)   path 'node_id'
                    , node_name       varchar2(30)    path 'node_name'
                     , node_value      varchar2(2000)  path 'node_value'
                      , parent_node_id  varchar2(100)   path 'parent_node_id'
                     , node_kind       varchar2(30)    path 'node_kind'
        ) x
        )
        select  JSONNest( 
                 JSONNestItem(
                   level
                 , json_object(
                     'name' value node_name
                   , 'value' value node_value
                   )
                , 'nested_info'
                )
              ) as result
        
        from edge_data t
        where node_kind in ('element', 'attribute', 'text')
        connect by prior node_id = parent_node_id
        start with parent_node_id is null ;

示例输出：-

{"name":"project","value":null,"child_info":[{"name":"projectNumber","value":"311927"},{"name":"projectType","value":"BUILD"},{"name":"lineOfBusiness","value":"COMMERCIAL"},{"name":"projectStatus","value":"PROGRASS"},{"name":"summary","value":null,"child_info":[{"name":"creationDate","value":"08/02/2016"},{"name":"workflowStateDate","value":null},{"name":"effectiveDate","value":"01/01/2014"},{"name":"clientRequested","value":"FALSE"},{"name":"mandatoryReview","value":"FALSE"},{"name":"internalProject","value":"FALSE"},{"name":"clientType","value":"Permanent"},{"name":"description","value":"Test Data 2"},{"name":"appliesTo","value":null,"child_info":[{"name":"Retail","value":"TRUE"},{"name":"Mail","value":"TRUE"}]}]}]}