CODEWITHSUNDEEP
xmlcountxquerydistinct-values
ritzdiamond
ritzdiamond

Reputation: 269

How to count each instance of distinct-values in XQuery?

I have an XML file with the following elements and attributes:

<element>
<unit id="1" color="blue"/>
<unit id="2" color="blue"/>
<unit id="3" color="red"/>
</element>

How do I get the count of 1) distinct occurrences of each attribute color and 2) count for each distinct occurrence?

So far, I've tried both distinct-values() and count() and their combinations, with no luck, ending with either the number of distinct attributes or the number of them, although I'd like to get both.

The list of results that I'd like to get would look like this:

Blue 2
Red 1

Upvotes: 6

Views: 12467

Answers (2)

Dimitre Novatchev
Dimitre Novatchev

Reputation: 243579

This is just an XPath 2,0 expression that seems shorter:

for $v in distinct-values(/*/*/@color)
 return
    ($v, count(index-of(/*/*/@color, $v)), '&#xA;')

When this XPath expression is evaluated against the provided XML document:

<element>
    <unit id="1" color="blue"/>
    <unit id="2" color="blue"/>
    <unit id="3" color="red"/>
</element>

the wanted, correct result is produced:

blue 2 
red 1

Upvotes: 6

Evan Lenz
Evan Lenz

Reputation: 4146

This query should do what you want:

let $input :=
  <element>
    <unit id="1" color="blue"/>
    <unit id="2" color="blue"/>
    <unit id="3" color="red"/>
  </element>

return
  for $value in distinct-values($input/unit/@color)
  let $count := count($input/unit[@color eq $value])
  order by $count descending
  return concat($value," ",$count)

This produces the following output for me in MarkLogic:

blue 2
red 1

UPDATE: I modified the query so it orders the result by the count value.

Upvotes: 13

Related Questions