Geek Answers Handbook

From XML to Path List in Oracle PL / SQL

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

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

How can I create an output list of all possible paths?

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

My need is this: I have to SELECT a lot of information from long XML, and I need to use XMLEXTRACT with all possible paths, so I would like to know if it is possible to "dbms_output.put_line" them automatically in a path.

I need a solution that does not depend on the name of the tags.

Assume the XML is well-formed.

Thank you in advance for your kind help.

  • In the second case:

How can I continue if the Oracle Java Extension has not been installed and I get 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

Please assume that I am not a DBA, and DBAs do not allow the installation of Oracle Java Extension.

+3
1

XMLTable 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'
  )

, , , .

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'
  )
+5

More articles:


All Articles