从XML到Oracle 12c中的路径列表 [英] From XML to list of paths in Oracle 12c

问题描述

从XML到其中的路径列表Oracle PL/SQL环境显示了如何列出XML文档中每个XML元素的所有XPATH及其对应的值.我的问题是如何列出XML元素的所有XPATH 和其属性的XPATH以及相应的值?

From XML to list of paths in Oracle PL/SQL environment shows how to list all the XPATHs of each XML element in an XML document and their corresponding values. My Question is how to list all the XPATHs of the XML elements and the XPATHs of their attributes and the corresponding values?

Oracle设置:

CREATE TABLE xml_data ( xml ) AS
  SELECT XMLTYPE('
    <ALFA>
      <BETA>0123</BETA> 
      <GAMMA attribute1="value1">2345</GAMMA> 
      <DELTA attribute2="value2"> 
         <EPSILON attribute3="value3" attribute4="value4">3</EPSILON> 
      </DELTA> 
    </ALFA> 
  ')
  FROM DUAL;

预期产量:

| ELEMENT_PATH                   | ELEMENT_TEXT |
|--------------------------------|--------------|
| ALFA                           | (null)       |
| ALFA/BETA                      | 0123         |
| ALFA/GAMMA                     | 2345         |
| ALFA/GAMMA/@attribute1         | value1       |
| ALFA/DELTA                     | (null)       |
| ALFA/DELTA/@attribute2         | value2       |
| ALFA/DELTA/EPSILON             | 3            |
| ALFA/DELTA/EPSILON/@attribute3 | value3       |
| ALFA/DELTA/EPSILON/@attribute4 | value4       | 

推荐答案

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE xml_data ( xml ) AS
  SELECT XMLTYPE('
    <ALFA>
      <BETA>0123</BETA> 
      <GAMMA attribute1="value1">2345</GAMMA> 
      <DELTA attribute2="value2"> 
         <EPSILON attribute3="value3" attribute4="value4">3</EPSILON> 
      </DELTA> 
    </ALFA> 
  ')
  FROM DUAL;   

查询1 :

select xpath, text
from   xml_data d
       CROSS JOIN
       XMLTable( 
         'for $i in $doc/descendant-or-self::*
            let $path := $i/string-join(ancestor-or-self::*/name(.), ''/'')
            return <data>{attribute path {$path}, attribute value {$i/text()}}</data>' 
         PASSING d.xml AS "doc"
         COLUMNS xpath varchar2(4000) path '/data/@path', 
                 text  varchar2(4000) path '/data/@value'
       )
UNION ALL
select xpath, text
from   xml_data d
       CROSS JOIN
       XMLTable( 
         'for $i in $doc/descendant-or-self::*
            let $path := $i/string-join(ancestor-or-self::*/name(.), ''/'')
            for $j in $i/attribute::*
              return <data>{attribute path { concat( $path, "/@", $j/name(.) ) }, attribute value {$j}}</data>' 
         PASSING d.xml AS "doc"
         COLUMNS xpath varchar2(4000) path '/data/@path', 
                 text  varchar2(4000) path '/data/@value'
       )

结果 :

Results:

|                          XPATH |   TEXT |
|--------------------------------|--------|
|                           ALFA | (null) |
|                      ALFA/BETA |   0123 |
|                     ALFA/GAMMA |   2345 |
|                     ALFA/DELTA | (null) |
|             ALFA/DELTA/EPSILON |      3 |
|         ALFA/GAMMA/@attribute1 | value1 |
|         ALFA/DELTA/@attribute2 | value2 |
| ALFA/DELTA/EPSILON/@attribute3 | value3 |
| ALFA/DELTA/EPSILON/@attribute4 | value4 |

这篇关于从XML到Oracle 12c中的路径列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！