Oracle XPath:父节点属性

Question

我是XPath新手;我已经找到了一种方法来做我想做的事情，但想知道是否有另一种方法可以节省我在代码中的一些重复。

我有这张表：

CREATE TABLE t (
    quark_p1        VARCHAR2(4)
,   quark_p2        VARCHAR2(7)
,   quark_p3        VARCHAR2(6)
,   one             VARCHAR2(1)
,   two             VARCHAR2(1)
,   three           VARCHAR2(1)
,   four            VARCHAR2(1)
,   five            VARCHAR2(1)
,   six             VARCHAR2(1)
,   seven           VARCHAR2(1)
,   eight           VARCHAR2(1)
,   nine            VARCHAR2(1)
)
;

下面的PL/SQL匿名块执行我想要的操作，从给定的XML中提取结构到此表中：

BEGIN
    INSERT INTO t (
        quark_p1
    ,   quark_p2
    ,   quark_p3
    ,   one
    ,   two
    ,   three
    ,   four
    ,   five
    ,   six
    ,   seven
    ,   eight
    ,   nine
    )
    SELECT x.quark_p1
    ,      x.quark_p2
    ,      x.quark_p3
    ,      x.one
    ,      x.two
    ,      x.three
    ,      x.four
    ,      x.five
    ,      x.six
    ,      x.seven
    ,      x.eight
    ,      x.nine
    FROM XMLTABLE('/BASIS/QUARK'
    PASSING XMLTYPE (
        '<BASIS>
             <QUARK P1="up" P2="charm" P3="bottom">
                 <NEST>
                     <ONE>A</ONE>
                     <TWO>B</TWO>
                     <THREE>C</THREE>
                     <FOUR>D</FOUR>
                     <FIVE>E</FIVE>
                     <SIX>F</SIX>
                     <SEVEN>G</SEVEN>
                     <EIGHT>H</EIGHT>
                     <NINE>I</NINE>
                 </NEST>
             </QUARK>
             <QUARK P1="up" P2="strange" P3="top">
                 <NEST>
                     <ONE>J</ONE>
                     <TWO>K</TWO>
                     <THREE>L</THREE>
                     <FOUR>M</FOUR>
                     <FIVE>N</FIVE>
                     <SIX>O</SIX>
                     <SEVEN>P</SEVEN>
                     <EIGHT>Q</EIGHT>
                     <NINE>R</NINE>
                 </NEST>
             </QUARK>
         </BASIS>')
    COLUMNS quark_p1 VARCHAR2(4) PATH '@P1'
    ,       quark_p2 VARCHAR2(7) PATH '@P2'
    ,       quark_p3 VARCHAR2(6) PATH '@P3'
    ,       one      VARCHAR2(1) PATH 'NEST/ONE'
    ,       two      VARCHAR2(1) PATH 'NEST/TWO'
    ,       three    VARCHAR2(1) PATH 'NEST/THREE'
    ,       four     VARCHAR2(1) PATH 'NEST/FOUR'
    ,       five     VARCHAR2(1) PATH 'NEST/FIVE'
    ,       six      VARCHAR2(1) PATH 'NEST/SIX'
    ,       seven    VARCHAR2(1) PATH 'NEST/SEVEN'
    ,       eight    VARCHAR2(1) PATH 'NEST/EIGHT'
    ,       nine     VARCHAR2(1) PATH 'NEST/NINE'
    ) x;
END;
/

这导致以下内容，这就是我所追求的：

SQL> SELECT * FROM t
  2  ;
QUARK_P1 QUARK_P2 QUARK_P3 O T T F F S S E N
-------- -------- -------- - - - - - - - - -
up       charm    bottom   A B C D E F G H I
up       strange  top      J K L M N O P Q R
SQL>

由于"NEST"级别经常重复，我想将其拉到起始节点中，并且仍然得到相同的结果。 我希望做如下事情：

BEGIN
    INSERT INTO t (
        quark_p1
    ,   quark_p2
    ,   quark_p3
    ,   one
    ,   two
    ,   three
    ,   four
    ,   five
    ,   six
    ,   seven
    ,   eight
    ,   nine
    )
    SELECT x.quark_p1
    ,      x.quark_p2
    ,      x.quark_p3
    ,      x.one
    ,      x.two
    ,      x.three
    ,      x.four
    ,      x.five
    ,      x.six
    ,      x.seven
    ,      x.eight
    ,      x.nine
    -- Notice, I changed the starting node from /BASIS/QUARK to /BASIS/QUARK/NEST....
    FROM XMLTABLE('/BASIS/QUARK/NEST'
    PASSING XMLTYPE (
        '<BASIS>
             <QUARK P1="up" P2="charm" P3="bottom">
                 <NEST>
                     <ONE>A</ONE>
                     <TWO>B</TWO>
                     <THREE>C</THREE>
                     <FOUR>D</FOUR>
                     <FIVE>E</FIVE>
                     <SIX>F</SIX>
                     <SEVEN>G</SEVEN>
                     <EIGHT>H</EIGHT>
                     <NINE>I</NINE>
                 </NEST>
             </QUARK>
             <QUARK P1="up" P2="strange" P3="top">
                 <NEST>
                     <ONE>J</ONE>
                     <TWO>K</TWO>
                     <THREE>L</THREE>
                     <FOUR>M</FOUR>
                     <FIVE>N</FIVE>
                     <SIX>O</SIX>
                     <SEVEN>P</SEVEN>
                     <EIGHT>Q</EIGHT>
                     <NINE>R</NINE>
                 </NEST>
             </QUARK>
         </BASIS>')
    -- ...and I changed all the paths here
    COLUMNS quark_p1 VARCHAR2(4) PATH '../@P1'
    ,       quark_p2 VARCHAR2(7) PATH '../@P2'
    ,       quark_p3 VARCHAR2(6) PATH '../@P3'
    ,       one      VARCHAR2(1) PATH 'ONE'
    ,       two      VARCHAR2(1) PATH 'TWO'
    ,       three    VARCHAR2(1) PATH 'THREE'
    ,       four     VARCHAR2(1) PATH 'FOUR'
    ,       five     VARCHAR2(1) PATH 'FIVE'
    ,       six      VARCHAR2(1) PATH 'SIX'
    ,       seven    VARCHAR2(1) PATH 'SEVEN'
    ,       eight    VARCHAR2(1) PATH 'EIGHT'
    ,       nine     VARCHAR2(1) PATH 'NINE'
    ) x;
END;
/

在我看来，这应该有效，但我收到此错误：

    FROM XMLTABLE('/BASIS/QUARK/NEST'
         *
ERROR at line 28:
ORA-06550: line 28, column 10:
PL/SQL: ORA-19110: unsupported XQuery expression
ORA-06550: line 2, column 5:
PL/SQL: SQL Statement ignored

SQL>    

我

错过了一些简单的东西，还是我不能从这里到达那里？

谢谢。

Answer 1

我惊讶地发现

如何在Oracle XPath表达式中获取父元素的名称？

如果您只是将"./"放在"../"