Is there an Oracle built in function that converts XML to JSON where the XML format varies?

Question

Using Oracle 12.2

Goal: Convert XML data in a CLOB to JSON

Desire: Use built in functionality of Oracle (does it even exist?)

I'm under the impression that Oracle CAN convert the XML to JSON on it's own. However, I am unable to find a straight forward answer or example in SO or Google and have never done something like this so I'm really uncertain how to proceed.

In Python it is 2 lines of code but am trying to put this into a Procedure on Oracle.

I don't have any XSLT files. I say files because looking at some of the historical data in the system I'm seeing 4 different formats of XML used and stored.

Need to pull all of the XML data and push as JSON to another table for testing.

Tried making sense from:

• Oracle: Convert XML to JSON
• How can i convert xml to json in oracle?
• Is there is any xml to json parser that can be used in oracle query?
• https://odieweblog.wordpress.com/tag/xml-to-json/ (This appears that we would need to know all the elements but we don't because we don't have the XSLT for any of the formats provided where some element:values exist and some do not when empty)
• https://docs.oracle.com/cd/E65459_01/dev.1112/e65461/content/conversion_xml_to_json.html (What filter? Only thing I see in SQL Developer for filter is ALWAYS non selectable)
• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/changes.html#GUID-C14C52AA-E651-4E79-BA23-58957F498FBF

Any information would be greatly appreciated.

Updated sample xml to json

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

Expect direct translation

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

Answer 1

You are not going to find a two-line solution as per python. Instead, you are going to need to parse the XML and generate the JSON. For that you can use XMLTABLE and JSON functions:

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

Which, for the sample data:

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;

Outputs:

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

---

Having said that you won't get a 2-line solution like python, you can technically get a 1-line solution in Oracle:

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

But that is just the same query as above with all the white-spaces concatenated. Number of lines of code is not necessarily a helpful metric to measure code by.

fiddle

Answer 2

Oracle has released APEX 22.2 which is fully supported through Oracle Support Services on all Editions (EE, SE2, SE, and SE1) of the Oracle database, 12.1.0.2 or higher with a valid Oracle Database Technical Support agreement.

For this example APEX 5 installation is enough to get started.

With that you can use the simple procedure given below to get the JSON object

          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;

if you want an alternative you can use the below but performance wise it is slower for large dataset.Also you need to implement this custom JSON nest units

        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 ;

Sample output:-

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