Oracle Goodies for XML and JSON

Published: Saturday, 27 September 2014 23:12 | Written by Stefan | Print | Email | Hits: 94649

XML and JSON are perfect to manage and transfer structured data on the internet. But in a relational database like oracle the data is stored in tables who can be linked via joins. This article will show how to get a XML or JSON out of a SQL statement which contains a master-detail dependency. For easy use, I put all together in a PL/SQL Package which you will find at the end of the article and in the download area.

Preparation

In our case study we have two tables to store persons and their phone numbers. Both tables are connected through a foreign key. See the following script for the definition of the tables:

create table ex01_person_tb (
  id number,
  name varchar2(50)
);
insert into ex01_person_tb values (1, 'Roger Waters');
insert into ex01_person_tb values (2, 'David Gilmour');
create table ex01_phone_tb (
  id number,
  person_id number,
  phone_number varchar2(50)
);
insert into ex01_phone_tb values (1, 1, '543 454433');
insert into ex01_phone_tb values (2, 1, '512 4776443');
insert into ex01_phone_tb values (3, 1, '521 6454423');
insert into ex01_phone_tb values (4, 2, '212 8332464');
insert into ex01_phone_tb values (5, 2, '312 6736423');

Just query the data to see what we have:

SQL> select * from ex01_person_tb;

        ID NAME
---------- --------------------------------------------------
         1 Roger Waters
         2 David Gilmour

SQL> select * from ex01_phone_tb;

        ID PERSON_ID PHONE_NUMBER
---------- ---------- --------------------------------------------------
         1          1 543 454433
         2          1 512 4776443
         3          1 521 6454423
         4          2 212 8332464
         5          2 312 6736423

SQL>

To connect these two tables, normaly we use a simle join. In our case this is not the way we want to display the data because if we do like this we will have repeating data (the name). To avoid this, we use a technic in oracle which is called CURSOR EXPRESSION to place detail information in the SQL statement. This is the perfect solution to present the data with all their dependencies like we are used to have it in a XML file. See the following example and the output we will get:

select a.id, a.name,
     cursor (select b.id, b.phone_number
               from ex01_phone_tb b
              where b.person_id = a.id) as phone_numbers
 from ex01_person_tb a;

SQL> select a.id, a.name,
2    cursor (select b.id, b.phone_number
3              from ex01_phone_tb b
4             where b.person_id = a.id) as phone_numbers
5 from ex01_person_tb a;

ID NAME PHONE_NUMBERS
---------- -------------------------------------------------- --------------------
1 Roger Waters CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

        ID PHONE_NUMBER
---------- --------------------------------------------------
         1 543 454433
         2 512 4776443
         3 521 6454423

2 David Gilmour CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

        ID PHONE_NUMBER
---------- --------------------------------------------------
         4 212 8332464
         5 312 6736423

SQL>

SQL to XML

In the next step we have to manage it, to transfer the result of the SQL in a way, to get a ready-to-use XML structure. Since the version 9 of the database, Oracle delivers a PL/SQL package called DBMS_XMLGEN which provides all the functionality we need. To hide the complexity and for easy using, I put it all together in a function with the SQL-statement as a parameter and the XML as the return value. To see how it works take a look at the following example:

  
declare
  l_sql_string varchar2(2000);
  l_xml        xmltype;
begin
  l_sql_string := 'select a.id, a.name, ' ||
                  '   cursor (select b.id, b.phone_number ' ||
                  '     from ex01_phone_tb b ' ||
                  '     where b.person_id = a.id ' ||
                  '     ) as phone_numbers ' || '  from ex01_person_tb a ';
 
  -- Create the XML aus SQL
  l_xml := itstar_xml_util.sql2xml(l_sql_string);
 
  -- Display the XML
  dbms_output.put_line(l_xml.getclobval());
end;

... and the code of the corresponding Package Function:

function sql2xml(i_sql_string in varchar2) return xmltype is
    l_context_handle dbms_xmlgen.ctxhandle;
    l_xml            xmltype;
    l_rows           number;
begin
    -- returns a new context handle to be used in the following functions
    l_context_handle := dbms_xmlgen.newcontext(i_sql_string);
    
	-- if null, give a empty tag (e.g. )
    dbms_xmlgen.setnullhandling(l_context_handle, dbms_xmlgen.empty_tag);
    
	-- get the XML
    l_xml  := dbms_xmlgen.getxmltype(l_context_handle, dbms_xmlgen.none);
    
	-- get back the number of rows
    l_rows := dbms_xmlgen.getnumrowsprocessed(l_context_handle);
    
	-- close the handle
    dbms_xmlgen.closecontext(l_context_handle);
    
	if l_rows > 0 then
      return(l_xml);
    else
      return(null);
    end if;
end;

... and the result of the dbms_output:

  
<ROWSET>
 <ROW>
  <ID>1</ID>
  <NAME>Roger Waters</NAME>
  <PHONE_NUMBERS>
   <PHONE_NUMBERS_ROW>
    <ID>1</ID>
    <PHONE_NUMBER>543 454433</PHONE_NUMBER>
   </PHONE_NUMBERS_ROW>
   <PHONE_NUMBERS_ROW>
    <ID>2</ID>
    <PHONE_NUMBER>512 4776443</PHONE_NUMBER>
   </PHONE_NUMBERS_ROW>
   <PHONE_NUMBERS_ROW>
    <ID>3</ID>
    <PHONE_NUMBER>521 6454423</PHONE_NUMBER>
   </PHONE_NUMBERS_ROW>
  </PHONE_NUMBERS>
 </ROW>
 <ROW>
  <ID>2</ID>
  <NAME>David Gilmour</NAME>
  <PHONE_NUMBERS>
   <PHONE_NUMBERS_ROW>
    <ID>4</ID>
    <PHONE_NUMBER>212 8332464</PHONE_NUMBER>
   </PHONE_NUMBERS_ROW>
   <PHONE_NUMBERS_ROW>
    <ID>5</ID>
    <PHONE_NUMBER>312 6736423</PHONE_NUMBER>
   </PHONE_NUMBERS_ROW>
  </PHONE_NUMBERS>
 </ROW>
</ROWSET>

XML to JSON

To get a JSON out of a XML the method transform of the Oracle xmltype. Therfore we have to have a XSLT stylesheet which contains all the rules to convert. For easy-use I placed the stylesheet in a string which was returned by a function. The entire string you will find in the appendix:

function get_xml_to_json_stylesheet return varchar2 is
    l_xslt_string varchar2(32000);
  begin
    l_xslt_string := '
...
';
    return(l_xslt_string);
end;
function xml2json(i_xml in xmltype) return xmltype is
    l_json xmltype;
begin
    l_json := i_xml.transform(xmltype(get_xml_to_json_stylesheet));
    return(l_json);
end;

The complete example in action:

declare
  l_sql_string varchar2(2000);
  l_xml        xmltype;
  l_json       xmltype;
begin
  l_sql_string := 'select a.id, a.name, ' ||
                  '   cursor (select b.id, b.phone_number ' ||
                  '     from ex01_phone_tb b ' ||
                  '     where b.person_id = a.id ' ||
                  '     ) as phone_numbers ' || '  from ex01_person_tb a ';
  -- Create the XML aus SQL
  l_xml := itstar_xml_util.sql2xml(l_sql_string);
  -- Display the XML
  dbms_output.put_line(l_xml.getclobval());
  -- convert the JSON
  l_json := itstar_xml_util.xml2json(l_xml);
  -- Display the JSON
  dbms_output.put_line(l_json.getclobval());
end;

... and the result of the dbms_output:

{"ROWSET": [
    {
      "ID": 1,
      "NAME": "Roger Waters",
      "PHONE_NUMBERS": [
        {
          "ID": 1,
          "PHONE_NUMBER": "543 454433"
        },
        {
          "ID": 2,
          "PHONE_NUMBER": "512 4776443"
        },
        {
          "ID": 3,
          "PHONE_NUMBER": "521 6454423"
        }
      ]
    },
    {
      "ID": 2,
      "NAME": "David Gilmore",
      "PHONE_NUMBERS": [
        {
          "ID": 4,
          "PHONE_NUMBER": "212 8332464"
        },
        {
          "ID": 5,
          "PHONE_NUMBER": "312 6736423"
        }
      ]
    }
  ]}

Appendix

Documents and Links

The XSLT

The XSLT to convert the XML to JSON

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!--
Copyright (c) 2006, Doeke Zanstra
All rights reserved.

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this
list of conditions and the following disclaimer. Redistributions in binary
form must reproduce the above copyright notice, this list of conditions and the
following disclaimer in the documentation and/or other materials provided with
the distribution.

Neither the name of the dzLib nor the names of its contributors may be used to
endorse or promote products derived from this software without specific prior
written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF
THE POSSIBILITY OF SUCH DAMAGE.
-->

<xsl:output indent="no" omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json"/>
<xsl:strip-space elements="*"/>

<xsl:variable name="d">0123456789</xsl:variable>

<xsl:template match="text()[preceding-sibling::node() or following-sibling::node()]"/>

<xsl:template match="text()">
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="."/>
    </xsl:call-template>
</xsl:template>

<xsl:template name="escape-string">
    <xsl:param name="s"/>
    <xsl:text>"</xsl:text>
    <xsl:call-template name="escape-bs-string">
      <xsl:with-param name="s" select="$s"/>
    </xsl:call-template>
    <xsl:text>"</xsl:text>
</xsl:template>

<xsl:template name="escape-bs-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,''\'')">
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''\''),''\\'')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-bs-string">
          <xsl:with-param name="s" select="substring-after($s,''\'')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
</xsl:template>

<xsl:template name="escape-quot-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,'';'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'';''),''"'')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="substring-after($s,'';'')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
</xsl:template>

<xsl:template name="encode-string">
    <xsl:param name="s"/>
    <xsl:choose>
      
      <xsl:when test="contains($s,'';'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'';''),''\t'',substring-after($s,'';''))"/>
        </xsl:call-template>
      </xsl:when>
      
      <xsl:when test="contains($s,'';'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'';''),''\n'',substring-after($s,'';''))"/>
        </xsl:call-template>
      </xsl:when>
      
      <xsl:when test="contains($s,'';'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'';''),''\r'',substring-after($s,'';''))"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise><xsl:value-of select="$s"/></xsl:otherwise>
    </xsl:choose>
</xsl:template>

<xsl:template match="text()[not(string(number())=''NaN'')]">
<xsl:value-of select="."/>
</xsl:template>

<xsl:template match="text()[translate(.,''TRUE'',''true'')=''true'']">true</xsl:template>
<xsl:template match="text()[translate(.,''FALSE'',''false'')=''false'']">false</xsl:template>

<xsl:template match="*[count(child::node())=0]">
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="local-name()"/>
    </xsl:call-template>
    <xsl:text>:null</xsl:text>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">}</xsl:if> 
</xsl:template>

<xsl:template match="*" name="base">
    <xsl:if test="not(preceding-sibling::*)">{</xsl:if>
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="name()"/>
    </xsl:call-template>
    <xsl:text>:</xsl:text>
    <xsl:apply-templates select="child::node()"/>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">}</xsl:if>
</xsl:template>

<xsl:template match="*[count(../*[name(../*)=name(.)])=count(../*) and count(../*)>1]">
    <xsl:if test="not(preceding-sibling::*)">[</xsl:if>
    <xsl:choose>
      <xsl:when test="not(child::node())">
        <xsl:text>null</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:apply-templates select="child::node()"/>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">]</xsl:if>
</xsl:template>