Problems visualizing the content of an xml in json format using clob

I am testing code to query a table, get the result as XML, convert it to JSON
format, and display it on screen for testing.
My problem is that, when showing the JSON in varchar format (with GetStringVal)
it works ok, but if the result is extensive, I show it as Clob (with GetClobVal).
In this case, the string shown includes the literal "& quot;" when it should
include double quotes.

Next, I show you 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

Thanks 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">  <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>  <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;

Not at all related to your problem, but you can use dbms_output.new_line; instead of dbms_output.put_line ('');. You may prefer the way you're doing it now anyway of course. (Also curious why you're going through XML and converting to JSON, when 12c supports JSON natively?)
– Alex Poole
Jun 29 at 10:33

dbms_output.new_line;

dbms_output.put_line ('');

1 Answer
1

Not sure why it's doing that and there's probably a better way to stop it, but as a workaround you can serliaze the XML into a CLOB variable; not entirely sure why that works either...

DECLARE l_xml XMLTYPE; l_json XMLTYPE; l_clob CLOB; BEGIN ... dbms_output.put_line (''); dbms_output.put_line ( 'Shows json as serialiaed clob'); select xmlserialize( content l_json as clob ) into l_clob from dual; dbms_output.put_line ( l_clob ); end; /

which shows

... Converting xml to json format... Shows json as varchar {"ROWSET":{"ROW":{"ID":4,"DENOPAIS":"Afganistán"}}} Shows json as clob {"ROWSET":{"ROW":{"ID":4,"DENOPAIS":"Afganistán"}}} Shows json as serialiaed clob {"ROWSET":{"ROW":{"ID":4,"DENOPAIS":"Afganistán"}}}

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Search This Blog

Mgiyuk