使用clob以json格式可视化xml内容的问题

Question

我正在测试查询表、获取XML格式的结果、将其转换为JSON格式并在屏幕上显示以进行测试的代码。我的问题是，当以varchar格式(使用GetStringVal)显示JSON时，它可以正常工作，但如果结果是广泛的，我将其显示为Clob (使用GetClobVal)。在这种情况下，显示的字符串包含文字"& quot;“，而它应该包含双引号。

接下来，我将向您展示用于将xml转换为json的函数，然后是一个匿名块，其中包含对一个小xml执行的测试。

我的数据库是Oracle 12.1

谢谢你对我的帮助。

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

Answer 1

我不确定它为什么这么做，而且可能有更好的方法来阻止它，但作为一种变通方法，您可以将XML插入到CLOB变量中；也不完全确定为什么这样做……

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;
/

它显示了

...
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"}}}

Answer 2

贡献解决方案的用户表示，他在几年前就对此发表了评论，here。

因为TRANSFORM方法返回XMLType的一个实例，所以当样式表的output方法设置为text时会有一些差异。

在这种情况下，正确的行为是什么？不要为了显示为明文而对字符实体进行转义，或者保持它们的原样以使XML内容保持有效。

参考出版物中提到的解决方法适用于Oracle11.2，在12c中行为似乎发生了一些变化。

即使如此，也可以使用类似的方法来获得预期的CLOB输出：

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.