Problems when viewing the content of an xml in json format using clob

Question

Problems when viewing the content of an xml in json format using clob

Navigation

#1 by (0 votes)

0

I am trying the code to consult a table, I get the result as XML I convert it into JSON format, and I show it on the screen to perform tests. My problem is that, by showing the JSON in varchar format (with GetStringVal) it works fine, but if the result is extensive, I show it as Clob (with GetClobVal). In this case, the string that is displayed includes the literal "& quot;" when should show double quotes

Next, I show the function that is applied to convert the xml to json, followed by an anonymous block with the tests performed on a small xml.

My database is Oracle 12.1

Thank you for your interest in helping me.

--Define a function with the XSLT to convert the XML to JSON
SET DEFINE OFF
  CREATE OR REPLACE 
  FUNCTION style_sheet_json 
  RETURN VARCHAR2 IS
    l_xslt VARCHAR2 ( 32000 );
  BEGIN
    l_xslt := '<?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>';

    RETURN ( l_xslt );

  END style_sheet_json;
/


DECLARE
l_xml XMLTYPE;
l_json XMLTYPE;
BEGIN

l_xml := XMLTYPE (
'<ROWSET>
 <ROW>
  <ID>4</ID>
  <DENOPAIS>Afganistán</DENOPAIS>
 </ROW>
</ROWSET>');

--Las 2 impresiones siguientes muestran lo mismo, el xml tal cual
dbms_output.put_line ( 'Shows xml as varchar');
dbms_output.put_line ( l_xml.GetStringVal());

dbms_output.put_line ('');
dbms_output.put_line ( 'Shows xml as clob');
dbms_output.put_line ( l_xml.GetClobVal());

dbms_output.put_line ('');
dbms_output.put_line ( 'Converting xml to json format...');
l_json := l_xml.TRANSFORM ( XMLTYPE ( style_sheet_json () ) );

dbms_output.put_line ('');
dbms_output.put_line ( 'Shows json as varchar');
dbms_output.put_line ( l_json.GetStringVal());

dbms_output.put_line ('');
dbms_output.put_line ( 'Shows json as clob');
dbms_output.put_line ( l_json.GetClobVal());

end;

json oracle plsql clob

asked by Jorge J. Espada 29.06.2018 в 12:12

source

1 answer

Convert longitude and latitude to angular direction 5 SQL query does not filter by combo

score 0 · Answer 1

The partner who contributed the solution indicates that he commented on this a few years ago here .

Since the TRANSFORM method returns an instance of XMLType , there are some discrepancies when the output method of the style sheet is set to text.

What is the correct behavior in this situation? Do not escape the entities of characters so that they appear as clear text, or keep them as they are for that the XML content remains valid.

The provisional solutions mentioned in the referenced publication apply to Oracle 11.2 , and it seems that the behavior has changed a bit in 12c .

Even so, similar approaches can be used to obtain the expected CLOB output:

DECLARE    
  l_xsl    xmltype := xmltype(style_sheet_json);  
  l_xml    xmltype;  
  l_json   xmltype;  
  l_out    clob;  

BEGIN  
  l_xml := XMLTYPE (  
'<ROWSET>  
<ROW>  
  <ID>4</ID>  
  <DENOPAIS>Afganistán</DENOPAIS>  
</ROW>  
</ROWSET>');  

  dbms_output.put_line('Using explicit conversion of character entities :');  
  l_json := l_xml.transform(l_xsl);  
  l_out := dbms_xmlgen.convert(l_json.getclobval(), dbms_xmlgen.ENTITY_DECODE);  
  dbms_output.put_line(l_out);  

  dbms_output.new_line;  
  dbms_output.put_line('Using SQL function XMLTransform :');  
  select xmltransform(l_xml, l_xsl)  
  into l_json  
  from dual;                

  l_out := l_json.getclobval;                
  dbms_output.put_line(l_out);                

  dbms_output.new_line;                
  dbms_output.put_line('Using SQL functions XMLTransform and XMLCast :');  
  select xmlcast(xmltransform(l_xml, l_xsl) as clob)   
  into l_out                
  from dual;                

  dbms_output.put_line(l_out);                

END;                
/                

Using explicit conversion of character entities :      
{"ROWSET":{"ROW":{"ID":4,"DENOPAIS":"Afganistán"}}}    

Using SQL function XMLTransform :                
{"ROWSET":{"ROW":{"ID":4,"DENOPAIS":"Afganistán"}}}    

Using SQL functions XMLTransform and XMLCast :         
{"ROWSET":{"ROW":{"ID":4,"DENOPAIS":"Afganistán"}}}    

PL/SQL procedure successfully completed.