Oracle的EXTRACT功能是否在XMLELEMENT中打破了NOENTITYESCAPING？ [英] Is Oracle's EXTRACT function breaking the NOENTITYESCAPING in the XMLELEMENT?

问题描述

Oracle 11g。我想出如果我将 NOENTITYESCAPING 添加到 XMLELEMENT 函数中，那么它很好地关闭实体转义。但是，当我将结果传递给 EXTRACT 时，转义似乎再次出现。

Oracle 11g. I figured out that if I add NOENTITYESCAPING to the XMLELEMENT function, it nicely turns off entity escaping. However, when I then pass the result to EXTRACT the escaping seems to come back again.

select xmlelement(NOENTITYESCAPING e,id,'->') 
  from (select level as id 
          from dual 
       connect by level < 6)      

XMLELEMENT(NOENTITYESCAPINGE,ID,'->')
---------------------------------------
<E>1-></E>
<E>2-></E>
<E>3-></E>
<E>4-></E>
<E>5-></E>

现在添加 EXTRACT ：

select xmlelement(NOENTITYESCAPING e,id,'->').extract('//text()')
  from (select level as id 
          from dual 
       connect by level < 6)

XMLELEMENT(NOENTITYESCAPINGE,ID,'->').EXTRACT('//TEXT()')
----------------------------------------------------------
1-&gt;
2-&gt;
3-&gt;
4-&gt;
5-&gt;

任何修复/解决方法以防止转义关闭？ 手册没有帮助。

Any fixes/workarounds to keep the escaping switched off? The manual gives no help.

推荐答案

尝试使用 extractvalue（）函数，它将编码的实体，而不是提取物（）。这是一个例子：

Try to use extractvalue() function, which unescapes encoded entities, instead of extract(). Here is an example:

clear screen;
column res format a20;

-- depending on a situation, NOENTITYESCAPING might be dropped

select extractvalue(
                     xmlelement(NOENTITYESCAPING e,id,'->')
                    , '//text()'
                    ) as res
  from (select level as id 
          from dual 
       connect by level < 6)

结果：

RES                
--------------------
1->                  
2->                  
3->                  
4->                  
5->    

但使用 extractvalue（）函数可能会受到只能返回一个节点的值的限制。在返回多个节点的值的情况下，可以使用 utl_i18n 包，并且可以使用该包的 unescape_reference（）函数清除编码实体：

But the use of extractvalue() function may be limited by the fact that it can return value of only one node. In a case of returning values of multiple nodes the utl_i18n package, and unescape_reference() function of that package can be used to unescape encoded entities:

clear screen;
column res format a20;

select utl_i18n.unescape_reference(xmlelement(root
                                             , xmlelement(node1, '>')
                                             , xmlelement(node2, '<')
                                             ).extract('//text()').getstringval()
                                   ) as res
 from dual
connect by level <= 3;

结果：

RES                
--------------------
><                   
><                   
>< 

---

是的，作为 utl_i18n .unescape_reference（）函数只接受 varchar2 数据类型和可以隐式转换为 varchar2 数据类型，当涉及到处理大的字符串时，你的手是绑定的。在这种情况下，您可以转到 dbms_xmlgen 包和 convert（）函数，它具有能够接受 CLOB 的重载版本。这是一个例子：

---

Yes, as utl_i18n.unescape_reference() function accepts only values of varchar2 data type and types that can be implicitly converted to the varchar2 data type, your hands are tied when it comes to processing large "strings". In this situation you may turn to dbms_xmlgen package and convert() function in particular, which has an overloaded version capable of accepting CLOBs. Here is an example:

select dbms_xmlgen.convert(
                           xmlagg(xmlelement(root
                                             , xmlelement(node1, '>')
                                             , xmlelement(node2, '<')
                                             )
                                  ).extract('//text()').getclobval()
                          , 1) as res
 from dual
connect by level <= 3000;   -- 1 (second parameter of the convert() function)
                            -- instructs function to decode entities  

结果：

RES
------------------------------------------------------
><><><><><><><><><><><><><><><><><><><><><><><><><>
-- ... the rest of the CLOB

这篇关于Oracle的EXTRACT功能是否在XMLELEMENT中打破了NOENTITYESCAPING？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！