CODEWITHSUNDEEP
substringxquery
ritzdiamond
ritzdiamond

Reputation: 269

Counting values obtained using the substring function in XQuery

I have an XML file with the following entries:

<file>
 <unit id="u-1.01"/>
 <unit id="u-1.02"/>
 <unit id="u-1.03"/>
 <unit id="u-1.04"/>
 <unit id="u-1.05"/>
 <unit id="u-1.06"/>
 <unit id="u-1.07"/>
 <unit id="u-2.01"/>
 <unit id="u-2.02"/>
 <unit id="u-2.03"/>
 <unit id="u-2.04"/>
 <unit id="u-2.05"/>
 <unit id="u-2.06"/>
</file>

Let us assume I use the substring function to select the third character of the id attribute and assign it to variable id:

    for $identifiers in file/unit/@id
    let $id := substring($identifiers, 3, 1)
    return ($id)

Which returns the following:

1 1 1 1 1 1 1 2 2 2 2 2 2

What I need is the count of each unique value: in this case, 7 for "1" and 6 for "2". How do I achieve this?

Upvotes: 1

Views: 485

Answers (2)

Jens Erat
Jens Erat

Reputation: 38682

If you use an XQuery 3.0 compatible processor (I tested using BaseX, use group by and count():

for $identifiers in file/unit/@id
let $id := substring($identifiers, 3, 1)
group by $id
return element { "count" } {
  attribute { "id" } { $id },
  count($identifiers)
}

Upvotes: 1

Jens Erat
Jens Erat

Reputation: 38682

If your XQuery processor does not support group by, fetch all unique IDs and count them on your own.

let $ids := file/unit/@id/substring(., 3, 1)
return
  for $uniqueID in distinct-values($ids)
  return element { "count" } {
    attribute { "id" } { $uniqueID },
      count($ids[.=$uniqueID])
    }

Upvotes: 1

Related Questions