How do I find duplicate data in xml document using XQuery?

Question

I have a bunch of documents in a MarkLogic xml database. One document has:

<colors>
  <color>red</color>
  <color>red</color>
</colors>

Having multiple colors is not a problem. Having multiple colors that are both red is a problem. How do I find the documents that have duplicate data?

score 3 · Accepted Answer · edited Jul 29 '13 at 22:22

3

Everything MarkLogic returns is just a sequence of nodes, so we can count the sequence size of the whole and compare it to the count of the sequence of distinct values. If they're not distinct, they're duplicate, and you have your subset.

for $c in doc()//colors
where fn:count($c/color) != fn:count(fn:distinct-values($c/color))
return $c

edited Jul 29 '13 at 22:22

jh314

27,144
16
62
82

answered Jan 11 '10 at 22:13

jaketrent

1,173
11
25

Depending on your scaling needs, this will run out of memory pretty quickly. See index-only approach below – derickson Jul 30 '13 at 01:20

score 2 · Answer 2 · edited Oct 26 '17 at 01:56

Or you could do it completely out of indexes :)

for $c in doc()//colors is likely to create an EXPANDED TREE CACHE error on larger data sets.

Here is a slightly more complicated way to attack this when the data is huge, make sure the URI Lexicon is turned on and then add a element range index on the element color and compute the distinct color values that have duplication somewhere. Then loop over only the documents that have this color one by one and compute the item-frequency counts of the colors of interest in the documents. If you get a frequency over 1, this document needs de-duplication.

let $qn := xs:QName("color")
let $colorsWithItemFreq := cts:element-values($qn, (), ("ascending", "item-order", "item-frequency"))
let $colorsOfInterest := 
    for $color at $i in cts:element-values($qn, (), ("ascending", "item-order", "fragment-frequency"))
    let $fragFrequency := cts:frequency($color)
    let $itemFrequency := cts:frequency($colorsWithItemFreq[$i])
    where $itemFrequency gt $fragFrequency
    return 
        $color

for $uri in cts:uris( (), ("document"), cts:element-value-query($qn, $colorsOfInterest)
let $colorsWithDuplicationInThisDoc :=
    for $color in cts:element-values($qn, (), ("item-frequency"), cts:document-query($uri) )
    where $color = $colorsOfInterest and cts:frequency($color) gt 1
    return
        $color
where fn:count( $colorsWithDuplicationInThisDoc ) gt 1
return
    $uri

Hope that helps.

score 2 · Answer 3 · edited Sep 04 '15 at 16:45

2

This should do the trick. I am not too familiar with MarkLogic, so the first line to get the set of documents may be wrong. This will return all documents which have 2 or more color elements with the same string value.

for $doc in doc()
let $colors = $doc//color/string(.)
where some $color in $colors
      satisfies count($colors[. = $color] > 1)
return doc()

edited Sep 04 '15 at 16:45

Dave Cassel

8,352
20
38

answered Jan 15 '09 at 10:04

Oliver Hallam

4,242
1
24
30

Is iterating over all of the documents the only way to go? – Sixty4Bit Jan 15 '09 at 14:43

score 1 · Answer 4 · answered Jan 26 '09 at 19:10

For this XML:

<?xml version="1.0"?>
<colors>
    <color>Red</color>
    <color>Red</color>
    <color>Blue</color>
</colors>

Using this XSD:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output method = "text" />  
    <xsl:strip-space elements="*"/>

    <xsl:template match="colors">

        <xsl:for-each select="color">
            <xsl:variable name="node_color" select="text()"/>
            <xsl:variable name="numEntries" select="count(../color[text()=$node_color])"/>
            <xsl:if test="$numEntries &gt; 1">
                <xsl:text>Color value of </xsl:text><xsl:value-of select="."/><xsl:text> has multiple entries &#xa;</xsl:text>      
            </xsl:if>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

I got this output:

Color value of Red has multiple entries 
Color value of Red has multiple entries

So that will at least find them, but it will report each occurrence of a repeated color, not just every repeated color.

How do I find duplicate data in xml document using XQuery?

4 Answers4