从 XML 到 Oracle PL/SQL 环境中的路径列表

2021-12-24 00:00:00 xml oracle plsql xmltype

请假设您有一个 XML 文件(例如，存储在还有一个 CLOB 列的 Oracle 表中):

Please suppose you have a XML file (stored, for example, in an Oracle table which has also a CLOB column):

<ALFA>
  <BETA>0123</BETA>
  <GAMMA>2345</GAMMA>
  <DELTA>
     <EPSILON>3</EPSILON>
  </DELTA>
</ALFA>

如何在输出中生成所有可能路径的列表?

How can I produce, in output, the list of all possible paths?

/ALFA/BETA/text()
/ALFA/GAMMA/text()
/ALFA/DELTA/EPSILON/text()

我的需求如下:我必须从长的 XML 中提取许多信息，并且我必须将 XMLEXTRACT 与所有可能的路径一起使用，所以我想知道是否可以自动dbms_output.put_line"它们方式.

My need is the following: I have to EXTRACT many information from a long XML and I have to use XMLEXTRACT with all possible paths, so I would like to know if is it possible to "dbms_output.put_line" them in an automatic way.

我需要一个独立于标签名称的解决方案.

I need a solution which is independent from the name of the tags.

请假设 XML 格式良好.

Please suppose that the XML is well-formed.

预先感谢您的帮助.

  • 第二种情况:

如果尚未安装 Oracle Java Extension，我该如何继续，并且收到以下错误?

How can I proceed if Oracle Java Extension has not been installed, and I receive the following error?

ORA-19112: error raised during evaluation:  
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

请假设我不是 DBA，并且 DBA 不授权安装 Oracle Java Extension.

Please suppose that I am not DBA, and DBA don't authorize Oracle Java Extension installation.

推荐答案

您可以使用 XMLTable 使用 XQuery 生成路径列表.

You can use XMLTable to produce list of paths with XQuery.

例如

(SQLFiddle)

with params as (
  select 
    xmltype('
      <ALFA>
        <BETA>0123</BETA>
        <GAMMA>2345</GAMMA>
        <DELTA>
           <EPSILON>3</EPSILON>
        </DELTA>
      </ALFA>
    ') p_xml
  from dual  
)    
select
  path_name || '/text()'
from
  XMLTable(
    '
      for $i in $doc/descendant-or-self::*
        return <element_path> {$i/string-join(ancestor-or-self::*/name(.), ''/'')} </element_path>
    '
    passing (select p_xml from params) as "doc"
    columns path_name varchar2(4000) path '//element_path'
  )

但这是一种错误的方式，至少因为它并不有效.

but it's a wrong way at least because it's not effective as it can.

只需使用相同的 XQuery 提取所有值:(SQLFiddle)

Just extract all values with same XQuery: (SQLFiddle)

with params as (
  select 
    xmltype('
      <ALFA>
        <BETA>0123</BETA>
        <GAMMA>2345</GAMMA>
        <DELTA>
           <EPSILON>3</EPSILON>
        </DELTA>
      </ALFA>
    ') p_xml
  from dual  
)    
select
  element_path, element_text
from
  XMLTable(
    '              
      for $i in $doc/descendant-or-self::*
        return <element>
                 <element_path> {$i/string-join(ancestor-or-self::*/name(.), ''/'')} </element_path>
                 <element_content> {$i/text()}</element_content>
               </element>  
    '
    passing (select p_xml from params) as "doc"
    columns 
      element_path   varchar2(4000) path '//element_path',
      element_text   varchar2(4000) path '//element_content'
  )

相关文章