XQuery - group by and count
xml, xquery
Solution
XQuery 3.0 solution with `group by`:
<A>{
for $name in //Name
let $full :=
if(not($name/@p)) then concat($name, ' ', $name/@t)
else concat($name, ' ', $name/@t, ', ', $name/@p)
group by $full
return <N>{$full, '-', count($name)}</N>
}</A>
XQuery 1.0 solution with `distinct-values($arg)`:
<A>{
let $names :=
for $name in //Name
return if(not($name/@p)) then concat($name, ' ', $name/@t)
else concat($name, ' ', $name/@t, ', ', $name/@p)
for $name in distinct-values($names)
return <N>{$name, '-', count($names[. eq $name])}</N>
}</A>
Problem
This is the structure of my XML file - ``` <A> <Name t="Mr.">James Bond</Name> <Name t="Mr.">Allen Bond</Name> <Name t="Mr." p="X">James Bond</Name> <Name t="Mr." p="X">James Bond</Name> <Name t="Mr.">James Bond</Name> <Name t="Mrs.">James Bond</Name> <Name t="Mrs.">James Bond</Name> <Name t="Mr.">James Bond</Name> <Name t="Mrs." p="Y">James Bond</Name> <Name t="Mrs." p="Y">James Bond</Name> </A> ``` The output I am expecting is - ``` <A> <N>Allen Bond Mr - 1</N> <N>James Bond Mr - 3</N> <N>James Bond Mr, X - 2</N> <N>James Bond Mrs - 2</N> <N>James Bond Mrs, Y - 2</N> </A> ``` I am able to get the distinct names but cant add the count...