Came across a function called jsontoxml in Oracle 18.3. Working with both JSON and XML I thought it might be useful. But at first, I found out that the function is not documented. The only reference I could find was via morgans library

Of course I had to test the function and see how it works…

So I created a table with one column (varchar2) to store the json string. Then I called the jsontoxml function, which properly converted my json string to xml string.

If you have access to Oracle Live SQL the script is located here. Otherwise if you have an Oracle database 18.3 and up here is the code.

rem ><(((*>~ Daniel Ekberg @dan_ekb
rem testing JSONTOXML new in 18.3 undocumented
rem create a table to store the json

set serveroutput on
create table jsondoc(doc varchar2(4000));

rem insert the json string
insert into jsondoc(doc) values(q'[{
     "firstName": "John",
     "lastName": "Smith",
     "age": 25,
     "address":
     {
         "streetAddress": "21 2nd Street",
         "city": "New York",
         "state": "NY",
         "postalCode": "10021"
     },
     "phoneNumber":
     [
         {
           "type": "home",
           "number": "212 555-1234"
         },
         {
           "type": "fax",
           "number": "646 555-4567"
         }
     ]
 }]');
 commit;

rem do the converson, set "root" as top level in xml
declare
 lv_xml varchar2(4000);
begin
 select JSONTOXML(doc,'root') into lv_xml from jsondoc;
 dbms_output.put_line(lv_xml);
end;

The conversion result is:

JSON:
{
     "firstName": "John",
     "lastName": "Smith",
     "age": 25,
     "address":
     {
         "streetAddress": "21 2nd Street",
         "city": "New York",
         "state": "NY",
         "postalCode": "10021"
     },
     "phoneNumber":
     [
         {
           "type": "home",
           "number": "212 555-1234"
         },
         {
           "type": "fax",
           "number": "646 555-4567"
         }
     ]
 }
XML:
<root>
   <firstName>John</firstName>
   <lastName>Smith</lastName>
   <age>25</age>
   <address>
      <streetAddress>21 2nd Street</streetAddress>
      <city>New York</city>
      <state>NY</state>
      <postalCode>10021</postalCode>
   </address>
   <phoneNumber>
      <type>home</type>
      <number>212 555-1234</number>
   </phoneNumber>
   <phoneNumber>
      <type>fax</type>
      <number>646 555-4567</number>
   </phoneNumber>
</root>

This is an interesting function which hopefully will be
officially documented in the future.

Over&Out