是否有Oracle内置的函数将XML转换为JSON,其中XML格式不同?

ycl3bljg  于 2023-05-06  发布在  Oracle
关注(0)|答案(2)|浏览(187)

使用Oracle 12.2
**目标:**将CLOB中的XML数据转换为JSON
**愿望:**使用Oracle的内置功能(是否存在?)

我的印象是Oracle可以自己将XML转换为JSON。然而,我无法在SO或Google中找到一个直接的答案或例子,并且从未做过这样的事情,所以我真的不确定如何继续。
在Python中,它是2行代码,但我试图将其放入Oracle上的Procedure中。
我没有任何XSLT文件。我之所以说文件,是因为查看系统中的一些历史数据,我看到使用和存储了4种不同格式的XML。
需要提取所有XML数据并以JSON形式推送到另一个表中进行测试。
试着从以下内容中理解:

任何信息将不胜感激。

示例xml更新为json

<elementA>
  <firstName>snoopy</firstName>
  <lastName>brown</lastName>
  <favoriteNumbers>
    <value>1</value>
    <value>2</value>
    <value>3</value>
  </favoriteNumbers>
</elementA>

期待直接翻译

{
  elementA:{
    firstName:'snoopy',
    lastName:'brown'
    favoriteNumbers:{
      value:[1,3,4]
    ]}
  }
}
3lxsmp7m

3lxsmp7m1#

你不会像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

kt06eoxx

kt06eoxx2#

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"}]}]}]}

相关问题