My Remarks!

the Life, the Universe and almost Everything what's Left

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/templates/stefans_blog_05/functions.php on line 182

me

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

info-14

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="*"/>
  <!--contant-->
  <xsl:variable name="d">0123456789</xsl:variable>

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

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

  <!-- Main template for escaping strings; used by above template and for object-properties
       Responsibilities: placed quotes around string, and chain up to next filter, escape-bs-string -->
  <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>

  <!-- Escape the backslash (\) before everything else. -->
  <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>

  <!-- Escape the double quote ("). -->
  <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,'';''),''&quot;'')"/>
        </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>

  <!-- Replace tab, line feed and/or carriage return by its matching escape code. Can''t escape backslash
       or double quote here, because they don''t replace characters (; becomes \t), but they prefix
       characters (\ becomes \\). Besides, backslash should be seperate anyway, because it should be
       processed first. This function can''t do that. -->
  <xsl:template name="encode-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <!-- tab -->
      <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>
      <!-- line feed -->
      <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>
      <!-- carriage return -->
      <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>

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

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

  <!-- item:null -->
  <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> <!-- MBR 30.01.2010: added this line as it appeared to be missing from stylesheet -->
  </xsl:template>

  <!-- object -->
  <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>

  <!-- array -->
  <xsl:template match="*[count(../*[name(../*)=name(.)])=count(../*) and count(../*)&gt;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>

  <!-- convert root element to an anonymous container -->
  <xsl:template match="/">
    <xsl:apply-templates select="node()"/>
  </xsl:template>

</xsl:stylesheet>

find me on Stack Overflow

Visits

Today 26 | Week 212 | Month 2409 | All 1147684

Latest Articles

Most Popular Articles last 24h

Most Popular Articles last Week

Challenges Completed

Vätternsee Club des Cinglés du Mont-Ventoux Styrkeproven 24hVelo Belchen³ Guitar Rehearsal

StackOverflow

profile for PT_STAR at Stack Overflow, Q&A for professional and enthusiast programmers

(c) 2014 Stefan Armbruster