Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Extract similar items from the same level

Gareth SMar 9 2016 — edited Mar 9 2016

Hi,

I can't quite seem to work out how to sequence two separate but related items at the same level.  I can do it for one part but not the other, the below is an example with the first step.

WITH xmltab AS (SELECT XMLType('

<OuterElement>

  <SomeElement>

    <NodeId>NodeABC1</NodeId>

    <PrimaryIP>

      <IPAddress>192.168.43.10</IPAddress>

      <IPAddress>192.168.43.63</IPAddress>

      <IPAddress>192.168.43.24</IPAddress>

      <IPAddress>192.168.43.64</IPAddress>

    </PrimaryIP>

    <SecondaryIP>

      <IPAddress>192.168.77.21</IPAddress>

      <IPAddress>192.168.77.118</IPAddress>

      <IPAddress>192.168.77.143</IPAddress>

      <IPAddress>192.168.77.87</IPAddress>

    </SecondaryIP>

  </SomeElement>

</OuterElement>') AS doc FROM dual)

SELECT xc.NodeId

      , pip.IPAddress AS PrimaryIP

   FROM xmltab xt

      , XMLTABLE('OuterElement' PASSING xt.doc

                 COLUMNS

                      NodeId VARCHAR2(50) PATH '/OuterElement/SomeElement/NodeId'

                    , PrimaryIP XMLType PATH '/OuterElement/SomeElement/PrimaryIP'

                    , SecondaryIP XMLType PATH '/OuterElement/SomeElement/SecondaryIP') xc

      , XMLTABLE('PrimaryIP/IPAddress' PASSING xc.PrimaryIP

                 COLUMNS

                      IPAddress VARCHAR2(20) PATH '.') pip;

This produces the output (as expected):

NODEIDPRIMARYIP
NodeABC1192.168.43.10
NodeABC1192.168.43.63
NodeABC1192.168.43.24
NodeABC1192.168.43.64

What I can't do is add the Secondary IP as another column, obviously if I pass it in as another XMLTABLE then I get a cartesian join, the output I am after is:

NODEIDPRIMARYIPSECONDARYIP
NodeABC1192.168.43.10192.168.77.21
NodeABC1192.168.43.63192.168.77.118
NodeABC1192.168.43.24192.168.77.143
NodeABC1192.168.43.64192.168.77.87

I'm sure this has been covered, but can't seem to find the right search terms.

Thanks in advance.

This post has been answered by odie_63 on Mar 9 2016
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 6 2016
Added on Mar 9 2016
2 comments
1,127 views