SQL, xQuery: Find duplicates

Question

I have a moderate knowledge of SQL and xpath, but none of xquery. How might I write a query to get all records where the XML contained within one field has two instances of the same element which also contains matching data of a sub element.

Let me explain by example:

Table: Fred
Table Fields: id, time, xmlone, xmltwo.
I want to find all records in this table where the xmlone has something like the below:

<!--xmlstart-->
<!--...any number of elements-->
<elementone position="1">
  <subelea>2010-01-01</subelea>
  <subeleb>hg72</subeleb>
  <subelec>George</subelec>
</elementone>
<!--...any number of elements here (may not end at same level)-->
    <elementone position="2">
      <subelea>2010-01-01</subelea>
      <subeleb>hg72</subeleb>
      <subelec>John</subelec>
    </elementone>
<!--xmlend-->

Note I want only matches where subelea and subeleb are matching. Also there may be duplicates of elementone, but I am not concerned with that unless the data from subelea and subeleb match.

Answer 1

(: Self-join on all <elementone/> :)
for $i in //elementone, $j in //elementone
where $i != $j
(: Compare `$i` and `$j` on the subelements that should be equal (or whatever) :)
and $i/subelea eq $j/subelea
and $i/subeleb eq $j/subeleb
(: Return result :)
return $i

If you need to take care of duplicates (on element level, does not care if two elements with exactly the same contents are in the input), wrap everything in parentheses and add /. (step operations take care of duplicates).

(
  for $i in //elementone, $j in //elementone
  where $i != $j
  and $i/subelea eq $j/subelea
  and $i/subeleb eq $j/subeleb
  return $i
)/.