Find duplicate child nodes in Oracle

Question

I have a column in oracle database that stores XML data.

For some reason, a query of mine was not checking for existing nodes while inserting it and hence has caused duplicates nodes in few rows.

I am not able to find an effective way to find those duplicates.

My XML looks like:

<myroot>
  <mydata>

    <myusers>
      <username>amy</username>
      <userrole/>
      <userrole>junior artist</userrole>
    </myusers>

    <myusers>
      <username>rosy</username>
      <userrole/>
      <userrole>junior artist</userrole>
    </myusers>

    <myusers>
      <username>timmy</username>
      <userrole>junior artist</userrole>
    </myusers>

  </mydata>
</myroot>

As shown in the above XML example, empty node is sitting idle in few tags of few rows but is there a way I can just find which rows contain duplicates ?

In past I've used below query to just extract this data but don't know how to extract data of two:

SELECT MYID, EXTRACT(MYDATA, 'myroot/mydata/myusers/userole/text()') 
FROM MYTABLE 
WHERE
EXISTNODE(MYDATA, 'myroot/mydata/myusers/userole') = 1

Just for background, my other data extract query is not sending below error is the reason why now the need to fix above data:

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

Answer 1

What finally worked for me was to find out the Count of "myusers" tags and "userrole". Where ever the count was mismatch were the rows which needed attention. I had 2000 rows and two of them had the duplicate so this solution worked fine for me. But if it was a different set of data, I think @Matthew McPeak's solution would work great.

I am sure there may be better and effective ways of doing this but below is the one-time query that worked fine for me.

SELECT * FROM 
    (
    select count(*) MYCON, MYID
        from MYTABLE  sa
        cross join xmltable('myroot/mydata/myusers'
            passing sa.MYDATA columns myusers varchar2(1) path '@dummy'
    ) 
    GROUP BY MYID ) BB
JOIN
    (
    select count(*) YOURCON, MYID
        from MYTABLE sa
        cross join xmltable('myroot/mydata/myusers/userrole'
            passing sa.MYDATA columns myusers varchar2(1) path '@dummy'
    ) 
    GROUP BY MYID ) AA
ON  AA.MYID = BB.MYID
WHERE MYCON <> YOURCON