Search code examples
oracle-databasexml-to-jsonoracle12.2

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

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:

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

  • 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