DevOps, CI/CD and Automation

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

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:

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

Thanks in advance.