Q:

Oracle - XMLType Column Comparison in Select Query

My database schema is as below:
ID  IDLANE  COMPONENT (XMLType COlumn)                  IDDATE  
--- ------  ---------                                   ------
111 111R1   <?xml version="1.0" encoding="utf-8"?>      2019-02-10T10:00:00
            <Rowsets>
            <Rowset Name="Item">
            <Row>
                <IDLANE>111R1</IDLANE>
                <ID>111</ID>
                <ORDER>0111000</ORDER>
                <ITEM>000010</ITEM>
                <MATERIAL>000000000257</MATERIAL>
            </Row>
            <Row>
                <IDLANE>111R1</IDLANE>
                <ID>111</ID>
                <ORDER>0111000</ORDER>
                <ITEM>000020</ITEM>
                <MATERIAL>000000000258</MATERIAL>
            </Row>
            </Rowset>
            </Rowsets>

111 111R2   <?xml version="1.0" encoding="utf-8"?>      2019-02-10T11:00:00
            <Rowsets>
            <Rowset Name="Item">
            <Row>
                <IDLANE>111R2</IDLANE>
                <ID>111</ID>
                <ORDER>0111000</VBELN>
                <ITEM>000010</ORDER>
                <MATERIAL>000000000257</MATERIAL>
            </Row>
            <Row>
                <IDLANE>111R2</IDLANE>
                <ID>111</ID>
                <ORDER>0111000</ORDER>
                <ITEM>000020</ITEM>
                <MATERIAL>000000000258</MATERIAL>
            </Row>
            </Rowset>
            </Rowsets>

222 222R1   <?xml version="1.0" encoding="utf-8"?>      2019-02-10T13:00:00
            <Rowsets>
            <Rowset Name="Item">
            <Row>
                <IDLANE>222R1</IDLANE>
                <ID>222</ID>
                <ORDER>0222000</ORDER>
                <ITEM>000010</ITEM>
                <MATERIAL>000000000257</MATERIAL>
            </Row>
            <Row>
                <IDLANE>222R1</IDLANE>
                <ID>111</ID>
                <ORDER>0222000</ORDER>
                <ITEM>000020</ITEM>
                <MATERIAL>000000000258</MATERIAL>
            </Row>
            </Rowset>
            </Rowsets>  

222 222R2   <?xml version="1.0" encoding="utf-8"?>      2019-02-10T14:00:00
            <Rowsets>
            <Rowset Name="Item">
            <Row>
                <IDLANE>222R2</IDLANE>
                <ID>222</ID
                <ORDER>0222000</ORDER>
                <ITEM>000010</ITEM>
                <MATERIAL>000000000259</MATERIAL
            </Row>

            </Rowset>
            </Rowsets>  

333 333R1   <?xml version="1.0" encoding="utf-8"?>      2019-02-10T18:00:00
            <Rowsets>
            <Rowset Name="Item">
            <Row>
                <IDLANE>333R1</IDLANE>
                <ID>333</ID
                <ORDER>0333000</ORDER>
                <ITEM>000010</ITEM>
                <MATERIAL>000000000259</MATERIAL
            </Row>

            </Rowset>
            </Rowsets>  

My requirement is to get all the IDs which :
exists more than one AND 
<ORDER> is same for all of them AND 
<ITEM> is same for all of them AND  
<MATERIAL> is same for all of them

For the data that I have provided, the query output should be :
ID    IDLANE    IDDATE
111   111R1     2019-02-10T10:00:00
111   111R2     2019-02-10T11:00:00

It wont include 222 because both 222 IDs have different Material.

I wrote the query to identify more than one ID as below:
SELECT ID FROM IDTABLE 
GROUP BY ID
HAVING COUNT(*) > 1

Above query gives me 111 & 222 as answers because both of the IDs exist more than one. But I need to compare XMLTyple COlumn's ,  and  too with each same ID appearing twice. But I do not know how to compare XML Type columns.
Can anyone provide some inputs.
Thanks

A:

I suppose you need rows with the same data in the columns you are interested in, but with different values ​​in the IDDATE column.
with
  t as (
    select
      111 as ID, '111R1' as IDLANE,
      xmltype(q'~
<Rowsets>
  <Rowset Name="Item">
    <Row>
      <IDLANE>111R1</IDLANE>
      <ID>111</ID>
      <ORDER>0111000</ORDER>
      <ITEM>000010</ITEM>
      <MATERIAL>000000000257</MATERIAL>
    </Row>
    <Row>
      <IDLANE>111R1</IDLANE>
      <ID>111</ID>
      <ORDER>0111000</ORDER>
      <ITEM>000020</ITEM>
      <MATERIAL>000000000258</MATERIAL>
    </Row>
  </Rowset>
</Rowsets>
~') as COMPONENT, timestamp'2019-02-10 10:00:00' as IDDATE from dual
    union all
    select
      111, '111R2',
      xmltype(q'~
<Rowsets>
  <Rowset Name="Item">
    <Row>
      <IDLANE>111R2</IDLANE>
      <ID>111</ID>
      <ORDER>0111000</ORDER>
      <ITEM>000010</ITEM>
      <MATERIAL>000000000257</MATERIAL>
    </Row>
    <Row>
      <IDLANE>111R2</IDLANE>
      <ID>111</ID>
      <ORDER>0111000</ORDER>
      <ITEM>000020</ITEM>
      <MATERIAL>000000000258</MATERIAL>
    </Row>
  </Rowset>
</Rowsets>
~'), timestamp'2019-02-10 11:00:00' from dual
    union all
    select
      222, '222R1',
      xmltype(q'~
<Rowsets>
  <Rowset Name="Item">
    <Row>
      <IDLANE>222R1</IDLANE>
      <ID>222</ID>
      <ORDER>0222000</ORDER>
      <ITEM>000010</ITEM>
      <MATERIAL>000000000257</MATERIAL>
    </Row>
    <Row>
      <IDLANE>222R1</IDLANE>
      <ID>111</ID>
      <ORDER>0222000</ORDER>
      <ITEM>000020</ITEM>
      <MATERIAL>000000000258</MATERIAL>
    </Row>
  </Rowset>
</Rowsets>
~'), timestamp'2019-02-10 13:00:00' from dual
    union all
    select
      222, '222R2',
      xmltype(q'~
<Rowsets>
  <Rowset Name="Item">
    <Row>
      <IDLANE>222R2</IDLANE>
      <ID>222</ID>
      <ORDER>0222000</ORDER>
      <ITEM>000010</ITEM>
      <MATERIAL>000000000259</MATERIAL>
    </Row>
  </Rowset>
</Rowsets>
~'), timestamp'2019-02-10 14:00:00' from dual
    union all
    select
      333, '333R1',
      xmltype(q'~
<Rowsets>
  <Rowset Name="Item">
    <Row>
      <IDLANE>333R1</IDLANE>
      <ID>333</ID>
      <ORDER>0333000</ORDER>
      <ITEM>000010</ITEM>
      <MATERIAL>000000000259</MATERIAL>
    </Row>
  </Rowset>
</Rowsets>
~'), timestamp'2019-02-10 18:00:00' from dual
  ),
  a as (
    select
      t."ID", t.IDLANE, t.IDDATE,
      count(*) over (
        PARTITION BY t."ID", x."order", x.ITEM, x.MATERIAL
      ) as qnt
    from t,
      xmltable('//Row'
        passing t.component
        columns
          "order" varchar2(10) path 'ORDER',
          item varchar2(10) path 'ITEM',
          material varchar2(20) path 'MATERIAL') x
  )
select distinct
  "ID", IDLANE, to_char(IDDATE, 'yyyy-MM-dd hh24:mi:ss') as IDDATE
from a where qnt > 1;

Output:
+-----+--------+---------------------+
| ID  | IDLANE |       IDDATE        |
+-----+--------+---------------------+
| 111 | 111R2  | 2019-02-10 11:00:00 |
| 111 | 111R1  | 2019-02-10 10:00:00 |
+-----+--------+---------------------+

Test it online with db<>fiddle.

