One thing that is really cool with Oracle is that you can almost do anything within the database.

In this post, I will show how to read an XML file and convert it to conventional rows. In the second example, I will read the same XML file and convert to rows in JSON format. This will be done by using the XMLDB and SQL/JSON functionality.

There is plenty of posts that describes how to convert XML to rows. But I haven ‘ t seen any about turning an XML file to JSON.

Let’s start by creating a simple XML file called smileyxml.xml.

<?xml version="1.0" encoding="UTF-8"?>
<smileyinfo>
   <smiley>
     <name>Happy</name>
      <desc>Face with a modest smile</desc>
    </smiley>
     <smiley>
     <name>Disappointed</name>
      <desc>Face with downcast eyes </desc>
    </smiley>
</smileyinfo>

Now we need a database directory that point to the location of the file.

create or replace directory xmlfiles as 'your path'

Be sure that os user(oracle) have the privilige to read/write to that location. Also the schema that executes the SQL must have the correct privilige on the database directory, in this case xmlfiles.

Let’s turn the text of the XML tags <name> and <desc> into rows.

SELECT extractvalue (a.COLUMN_VALUE,'//name') smiley_name,
       extractvalue (a.COLUMN_VALUE,'//desc') description
 FROM
   TABLE (
      xmlsequence (
         extract (
            xmltype(bfilename ('xmlfiles', 'smileyxml.xml') --Location
                   ,NLS_CHARSET_ID ('UTF8'))                --Charset 
                  ,'/smileyinfo/smiley'))) a ;              --Starting point
SMILEY_NAME	DESCRIPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          ------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                          
Happy           Face with a modest smile                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
Disappointed    Face with downcast eyes 

By using XMLDB functionality we can easily extract the tags we want.

Now if we want to turn the XML file inte JSON I will use the JSON_OBJECTAGG function which was introduced in Oracle 12.2. The SQL/JSON function JSON_OBJECTAGG  is an aggregate function. It accepts two arguments( property key-value pair ), the first of these being used as a key and the second as a value.  It returns a single JSON object that contains those object members.

SELECT JSON_OBJECTAGG( KEY smiley_name VALUE description ) as jsonout
 FROM   
    XMLTABLE(
         '/smileyinfo/smiley' --starting point
     PASSING XMLTYPE(
                     BFILENAME ('xmlfiles', 'smileyxml.xml'),--location
                     NLS_CHARSET_ID ('UTF8')--encoding
                     )
         COLUMNS smiley_name   VARCHAR2(200) PATH './/name',--which tag
                 description VARCHAR2(200) PATH './/desc' --which tag
       );
JSONOUT
---------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                          

{"Happy":"Face with a modest smile","Disappointed":"Face with downcast eyes "}

According to the Oracle docs, KEY is optional and is provided for semantic clarity.

Is there a use case for this? You can of course apply this to other source like webservices or whatever. So I’m sure there are plenty of it. My use case was to learn something new and write a post about it.

Over&Out