Xquery finding duplciate IDs

Question

I have an XML database which contains elements which have an id. These are all unique. They also have a secondary identifier which links them to a similar object in another database. These are not all unique.

Is there an XQuery which would let me identify all the non-unique IDs? I can count how many there are using distinct-values(), but that doesn't help identify the IDs which have duplicates!

Example XML: (each object is contained in a separate file in the eXist database)

<object id="uniqueID123">
  <secondary identifier="nonUnique888"/>
</object>

<object id="uniqueID456">
  <secondary identifier="nonUnique888"/>
</object>

<object id="uniqueID789">
  <secondary identifier="Unique999"/>
</object>

I would want to identify the duplicated string "nonUnique888".

@user320425: Semanticly this is duplicate of [this](http://stackoverflow.com/questions/133092/how-do-you-identify-duplicate-elements-in-an-xpath-20-sequence#287360) because XQuery is a superset of XPath. — , Oct 06 '10 at 20:41
Wow, $vSeq[index-of($vSeq,.)[2]] is indeed a very elegant solution! I didn't realise the index-of() worked like that, too used to the Java find-the-first style. — Nick, Oct 07 '10 at 09:23
@user320425: Good question (+1). Read my answer which I hope contains the shortest solution. — Dimitre Novatchev, Oct 07 '10 at 18:51

score 3 · Accepted Answer · answered Oct 06 '10 at 18:49

3

The following query returns all non unique identifiers:

let $sec := doc('source')/root/object/secondary
for $id in distinct-values($sec/@identifier)
where count($sec[@identifier eq $id]) gt 1
return $id

answered Oct 06 '10 at 18:49

Shcheklein

5,979
7
44
53

score 2 · Answer 2 · answered Oct 07 '10 at 18:49

2

Use:

let $vSeq := /object/secondary/@identifier
  return
    $vSeq[index-of($vSeq,.)[2]]

Read the explanation here.

answered Oct 07 '10 at 18:49

Dimitre Novatchev

240,661
26
293
431

score 0 · Answer 3 · answered May 10 '16 at 09:55

use this code store in xml file

let $path:="/db/test/all.xml"
let $a := xmldb:store( $col,'adub.xml',<root></root>)  

let $sec := doc($path)//profile
for $id in distinct-values($sec/mail)
where count($sec[mail eq $id]) gt 1
return 
 update insert  
            <profile>
                {$id}
                </profile>
   into  doc($a)/root

Xquery finding duplciate IDs

3 Answers3