CODEWITHSUNDEEP
sqlxmloracle-databasexpathxmltype
Tebbe
Tebbe

Reputation: 1372

Oracle XPath: Parent Node Attribute?

I'm an XPath newbie; I've found a way to do what I want, but wonder if there's another way that can save me some repetition in my code.

I have this table:

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)
)
;

The following PL/SQL anonymous block does what I want, extracting into this table the structure from my given 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;
/

This results in the following, which is what I'm after:

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>

Since the "NEST" level is repeated so often, I'd like to pull it up into the starting node, and still get the same results. I'm looking to do something like the following:

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;
/

This seems to me like it should work, but I get this error:

    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>

Am I missing something simple, or can I not get there from here?

Thanks.

Upvotes: 3

Views: 2570

Answers (2)

PraveenSQL
PraveenSQL

Reputation: 1

Hope this will work. IN XMLTYPE having some limitation so pass CLOB XMLTYPE.
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'
--changed here
    PASSING XMLTYPE (XMLTYPE.GETCLOBVAL(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 '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;

Upvotes: 0

Andy Steeley
Andy Steeley

Reputation: 41

I was surprised to find from

How to get the name of the parent element in an Oracle XPath expression?

that it works if you just put "./" in front of the "../"

Upvotes: 4

Related Questions