Forum Stats

  • 3,874,985 Users
  • 2,266,790 Discussions
  • 7,912,041 Comments

Discussions

Categories

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.

· Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
This discussion has been closed.