Forum Stats

  • 3,872,051 Users
  • 2,266,372 Discussions
  • 7,911,041 Comments

Discussions

Extract similar items from the same level

Gareth S
Gareth S Member Posts: 88 Blue Ribbon
edited Mar 9, 2016 9:47AM in XQuery

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.

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Mar 9, 2016 7:17AM Answer ✓

    Hi,

    I blogged about this situation a little while ago :

    https://odieweblog.wordpress.com/2015/06/19/how-to-parse-repeating-groups-correlated-by-position-as-relational-data/

    For example, using a pure XQuery approach :

    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.* 
       FROM xmltab xt 
          , XMLTABLE(
              'for $i in /OuterElement/SomeElement
                 , $j at $p in $i/PrimaryIP/IPAddress
               return element r {
                 $i/NodeId
               , element PrimaryIP {data($j)}
               , element SecondaryIP {data($i/SecondaryIP/IPAddress[$p])}
               }'
              PASSING xt.doc 
              COLUMNS 
                NodeId      VARCHAR2(50) PATH 'NodeId' 
              , PrimaryIP   VARCHAR2(20) PATH 'PrimaryIP' 
              , SecondaryIP VARCHAR2(20) PATH 'SecondaryIP'
            ) xc 
    ;
    
    

    You'll see in the post that the alternative approach using PIVOT is much more efficient on large XMLs.

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Mar 9, 2016 7:17AM Answer ✓

    Hi,

    I blogged about this situation a little while ago :

    https://odieweblog.wordpress.com/2015/06/19/how-to-parse-repeating-groups-correlated-by-position-as-relational-data/

    For example, using a pure XQuery approach :

    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.* 
       FROM xmltab xt 
          , XMLTABLE(
              'for $i in /OuterElement/SomeElement
                 , $j at $p in $i/PrimaryIP/IPAddress
               return element r {
                 $i/NodeId
               , element PrimaryIP {data($j)}
               , element SecondaryIP {data($i/SecondaryIP/IPAddress[$p])}
               }'
              PASSING xt.doc 
              COLUMNS 
                NodeId      VARCHAR2(50) PATH 'NodeId' 
              , PrimaryIP   VARCHAR2(20) PATH 'PrimaryIP' 
              , SecondaryIP VARCHAR2(20) PATH 'SecondaryIP'
            ) xc 
    ;
    
    

    You'll see in the post that the alternative approach using PIVOT is much more efficient on large XMLs.

  • Gareth S
    Gareth S Member Posts: 88 Blue Ribbon
    edited Mar 9, 2016 9:47AM

    Thanks, absolutely spot on (of course! ) and great blog post as well.  In my case the the method above (method 1 in the blog) works best for me, without the hint.  I suspect that this is because the XML is part of a much larger document that has an XSD registered for it, so the query as a whole works better and the Primary/Secondary IP addresses are generally few in number - approx 4 per XML document.

    Many thanks for your help.

This discussion has been closed.