XML to JSON and JSON to XML

Below is an example to create JSON data from XML.

For more details on XML, and its usage you can refer to

To Store and Select XML Data 

Create the table to store XML data

CREATE TABLE HR.EMP_XML_DATA 
   (	EMPLOYEE_ID NUMBER, 
    	EMP_DETAILS_XML XMLTYPE, 
	    INSERT_DATE DATE, 
	    INSERT_USER_ID NUMBER
   )
TABLESPACE USERS 
   XMLTYPE COLUMN EMP_DETAILS_XML STORE AS BASICFILE CLOB (
  TABLESPACE USERS ) ;

We insert sample data

INSERT INTO HR.EMP_XML_DATA VALUES (   208,   SYS.XMLTYPE.CREATEXML('<EmployeeInformation 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd">
  <EmployeeDetails>
    <name>William Gietz</name>
    <email>wgietz2@oramail.com</email>
    <telephone>515.123.8181</telephone>
	<job>Public Accountant</job>
	<salary>8390</salary>
	<hire_date>24-AUG-2001</hire_date>
  </EmployeeDetails>
  <EmpJobHistoryDetails>
    <Job Id="AD_ASST">
      <Description>Administration Assistant</Description>
      <JobDetail Department="Executive" StartDate="17-SEP-1995" EndDate="17-JUN-2001"/>
    </Job>
    <Job Id="FI_ACCOUNT">
      <Description>Accountant</Description>
      <JobDetail Department="Accounting" StartDate="18-JUN-2001" EndDate="30-JUN-2002"/>
    </Job>
    <Job Id="AC_ACCOUNT">
      <Description>Accountant</Description>
      <JobDetail Department="Accounting" StartDate="01-JUL-2002" EndDate="30-NOV-2016"/>
    </Job>
  </EmpJobHistoryDetails>
</EmployeeInformation>'),SYSDATE, 10);  
 
INSERT INTO HR.EMP_XML_DATA VALUES(   209,   SYS.XMLTYPE.CREATEXML(
      '<EmployeeInformation 
	  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
	  xsi:nonamespaceschemalocation="http://www.oracle.com/xdb/po.xsd">
  <EmployeeDetails>
    <name>Stephen King</name>
    <email>sking@oramail.com</email>
    <telephone>515.123.8271</telephone>
	<job>Accountant</job>
	<salary>4500</salary>
	<hire_date>4-AUG-1995</hire_date>
  </EmployeeDetails>  <EmpJobHistoryDetails>
  </EmpJobHistoryDetails>
</EmployeeInformation>'), SYSDATE, 10);

To convert this data to JSON we first convert it to tabular format using XMLTABLE.

SELECT X.* 
FROM EMP_XML_DATA, 
XMLTABLE ('$d/EmployeeInformation/EmployeeDetails' passing EMP_DETAILS_XML as "d" 
   COLUMNS 
   name    VARCHAR(100)     PATH 'name',
   email    VARCHAR(20)     PATH 'email',
   job     VARCHAR(25)     PATH 'job') AS X;

NAME	EMAIL	JOB
Stephen King	sking@oramail.com	Accountant
William Gietz	wgietz2@oramail.com	Public Accountant

Once it is in table format, we can use JSON_OBJECT and JSON_ARRAYAGG as below

SELECT
    JSON_ARRAYAGG(
        JSON_OBJECT(KEY 'emp-'||to_char(rownum) VALUE
            JSON_OBJECT(
                     KEY 'name' VALUE t.name,
                     KEY 'email' VALUE t.email,
                     KEY 'job' VALUE t.job
                   )
                )
            )				
FROM (  
        SELECT X.* 
FROM EMP_XML_DATA, 
XMLTABLE ('$d/EmployeeInformation/EmployeeDetails' passing EMP_DETAILS_XML as "d" 
   COLUMNS 
   name    VARCHAR(100)     PATH 'name',
   email    VARCHAR(20)     PATH 'email',
   job     VARCHAR(25)     PATH 'job') AS X 
) t; 

Output for the same is as below

  [
  {
    "emp-1": {
      "name": "Stephen King",
      "email": "sking@oramail.com",
      "job": "Accountant"
    }
  },
  {
    "emp-2": {
      "name": "William Gietz",
      "email": "wgietz2@oramail.com",
      "job": "Public Accountant"
    }
  }
]
  

Below is an example to create XML data from JSON. 

 To create the table and JSON data we can use the link below

  Oracle JSON : Select Level 1 and Level 2 data

We create the query to select JSON format data in table form using JSON_TABLE as below.

SELECT 
 jt.studentFirstName , jt.studentLastName, jt.studentRollNo, jt.studentLocation, studentWorkSite
FROM
STUDENT_PROFILE sp ,
JSON_TABLE (
         STUDENT_DATA,
        '$[*]'
         COLUMNS (
          studentFirstName  VARCHAR2(100) PATH '$.first_name', 
          studentLastName   VARCHAR2(100) PATH '$.last_name',
          studentRollNo NUMBER PATH '$.student_roll_no',
          studentLocation VARCHAR2(100) PATH '$.location',
          studentWorkSite VARCHAR2(100) PATH '$.websites[0].URL',
          studentTutorialSite VARCHAR2(100) PATH '$.websites[1].URL',
          studentSocialSite_1 VARCHAR2(100) PATH '$.social_media[0].link',
          studentSocialSite_3 VARCHAR2(100) PATH '$.social_media[2].link'
        ) )as jt
WHERE STUDENT_ROLL_NO <= 10004;

Output is as below

STUDENTFIRSTNAME	STUDENTLASTNAME	STUDENTROLLNO	STUDENTLOCATION	STUDENTWORKSITE
Alcaraz	A	10002	Spain	https://www.theoraclereference.blogspot.com/
Sasha	Zverev	10003	Germany	https://www.theoraclereference.blogspot.com/
Tom	Jackson	10001	California	https://www.theoraclereference.blogspot.com/
Rafael	Nadal	10004	Spain	https://www.theoraclereference.blogspot.com/

We now use DBMS_XMLGEN.GETXMLTYPE to convert this JSON format data into XML as below

SELECT
    DBMS_XMLGEN.GETXMLTYPE
    (q'[
SELECT 
 jt.studentFirstName , jt.studentLastName, jt.studentRollNo, jt.studentLocation, studentWorkSite
FROM
STUDENT_PROFILE sp ,
JSON_TABLE (
         STUDENT_DATA,
        '$'
         COLUMNS (
          studentFirstName  VARCHAR2(100) PATH '$.first_name', 
          studentLastName   VARCHAR2(100) PATH '$.last_name',
          studentRollNo NUMBER PATH '$.student_roll_no',
          studentLocation VARCHAR2(100) PATH '$.location',
          studentWorkSite VARCHAR2(100) PATH '$.websites[0].URL',
          studentTutorialSite VARCHAR2(100) PATH '$.websites[1].URL',
          studentSocialSite_1 VARCHAR2(100) PATH '$.social_media[0].link',
          studentSocialSite_3 VARCHAR2(100) PATH '$.social_media[2].link'
        ) )as jt
        ]')
FROM DUAL;		

You will get the xml output data as below

<?xml version="1.0" encoding="UTF-8"?>
<ROWSET>
   <ROW>
      <STUDENTFIRSTNAME>Alcaraz</STUDENTFIRSTNAME>
      <STUDENTLASTNAME>A</STUDENTLASTNAME>
      <STUDENTROLLNO>10002</STUDENTROLLNO>
      <STUDENTLOCATION>Spain</STUDENTLOCATION>
      <STUDENTWORKSITE>https://www.theoraclereference.blogspot.com/</STUDENTWORKSITE>
   </ROW>
   <ROW>
      <STUDENTFIRSTNAME>Sasha</STUDENTFIRSTNAME>
      <STUDENTLASTNAME>Zverev</STUDENTLASTNAME>
      <STUDENTROLLNO>10003</STUDENTROLLNO>
      <STUDENTLOCATION>Germany</STUDENTLOCATION>
      <STUDENTWORKSITE>https://www.theoraclereference.blogspot.com/</STUDENTWORKSITE>
   </ROW>
   <ROW>
      <STUDENTFIRSTNAME>Tom</STUDENTFIRSTNAME>
      <STUDENTLASTNAME>Jackson</STUDENTLASTNAME>
      <STUDENTROLLNO>10001</STUDENTROLLNO>
      <STUDENTLOCATION>California</STUDENTLOCATION>
      <STUDENTWORKSITE>https://www.theoraclereference.blogspot.com/</STUDENTWORKSITE>
   </ROW>
   <ROW>
      <STUDENTFIRSTNAME>Rafael</STUDENTFIRSTNAME>
      <STUDENTLASTNAME>Nadal</STUDENTLASTNAME>
      <STUDENTROLLNO>10004</STUDENTROLLNO>
      <STUDENTLOCATION>Spain</STUDENTLOCATION>
      <STUDENTWORKSITE>https://www.theoraclereference.blogspot.com/</STUDENTWORKSITE>
   </ROW>
</ROWSET>

References 

How to generate, parse and use JSON data

Thought for the day

Do no evil and evil will never overtake you.

  Stay away from wrong, and it will turn away from you.

Do not sow in the furrows of injustice,

   and you will not reap a sevenfold crop.

Sirach 7:1-3