Xquery grouping results based on condition

Question

I have a question on xquery grouping. From what I understand, the usual case would be to use distinct-values on the docs in the for loop, however as I have a condition to fulfill before grouping the values, and I am not quite sure how this can be done.

This is part of my xmldb:

<element tag="0001,0000" name="Pos1>198</element>
<element tag="0001,0001" name="Pos2">123</name>
<element tag="0002,0001" name="Pos3">433</element>
<element tag="000b,0000" name="Pos3">16</element>
<element tag="0005,0000" name="Pos4>532</element>
<element tag="0005,0001" name="Pos5">342</name>
<element tag="0008,0001" name="Pos6">17</element>

The condition to fulfill is that the x-coordinates (number or hexdec) have to be in odd values (e.g. from the above xml, i would only need results from tag="0001,...", tag="000b,...", tag="0005,..."), and then count how many items in each group.

This is how the results should look like:

<group>
<element xcoord="0001">2</element>
<element xcoord="000b">1</element>
<element xcoord="0005">2</element>
</group>

My xquery code so far looks like this, where I could generate results that have odd x-coordinates, but I have no idea how to proceed on from here for the grouping.

import module namespace functx="http://www.functx.com" at "http://www.xqueryfunctions.com/xq/functx-1.0-nodoc-2007-01.xq"; 

for $x in collection('/db/mapdb/')//element
let $coord := number(functx:substring-before-last($x/@tag, ","))
where $coord mod 2 != 0
return $x

Kindly advise me. Thank you very much.

Answer 1

This XQuery:

for $x in 
  distinct-values(/*/*/substring-before(@tag,',')
                    [contains('13579bdf', 
                               substring(., string-length(.))
                              )
                     ]
                  )
 return
   <element xcoord="{$x}">
    {count(/*/*[$x eq substring-before(@tag,',')])}
   </element>

produces the wanted, correct result:

<element xcoord="0001">2</element>
<element xcoord="000b">1</element>
<element xcoord="0005">2</element>

Answer 2

This XQuery:

<group>{
    for $key in distinct-values(/root/element/tokenize(@tag,',')[1])
                   [contains('13579bdf',substring(.,string-length(.)))]
    return <element xcoord="{$key}">{
               count(/root/element[tokenize(@tag,',')[1] eq $key])
          }</element>
}</group>

With this input:

<root>
    <element tag="0001,0000" name="Pos1">198</element>
    <element tag="0001,0001" name="Pos2">123</element>
    <element tag="0002,0001" name="Pos3">433</element>
    <element tag="000b,0000" name="Pos3">16</element>
    <element tag="0005,0000" name="Pos4">532</element>
    <element tag="0005,0001" name="Pos5">342</element>
    <element tag="0008,0001" name="Pos6">17</element>
</root>

Output:

<group>
    <element xcoord="0001">2</element>
    <element xcoord="000b">1</element>
    <element xcoord="0005">2</element>
</group>

Note: There is no built-in operator for xs:hexBinary data type except for eq and ne.

Answer 3

You didn't say which processor you are using. I tested the following code on try.zorba-xquery.com (it should also work for Saxon or other processors supporting XQuery 1.1 and XPath 2.0):

let $xmldb :=
  (
    <element tag="0001,0000" name="Pos1">198</element>,
    <element tag="0001,0001" name="Pos2">123</element>,
    <element tag="0002,0001" name="Pos3">433</element>,
    <element tag="000b,0000" name="Pos3">16</element>,
    <element tag="0005,0000" name="Pos4">532</element>,
    <element tag="0005,0001" name="Pos5">342</element>,
    <element tag="0008,0001" name="Pos6">17</element>
  )
for $x in $xmldb
let $coord := tokenize($x/@tag, ",")[1]
group by $coord
where contains('13579bdf',substring($coord,string-length($coord)))
return <element xcoord="{$coord}">{count($x)}</element>

Hope that helps?

Answer 4

If you are using an XQuery 1.0 processor then the usual pattern for a group by is:

let $values := ...
for $key in distinct-values(for $value in $values return my:key($value))
let $group := $values[my:key(.)=$key]
return ...

where my:key is a function/expression that obtains the key for each input value.

In your case you can fill in the pattern as so:

for $coord in distinct-values($xmldb/(tokenize(.,",")[1]))
let $x := $xmldb[tokenize(.,",")[1] = $coord]
return <element xcoord="{$coord}">{count($x)}</element>

Whether or not this perform efficiently depends on your implementation. I have written a blog post on how to write a group by that XQSharp will recognise.

Edit: I missed the requirement that all x coordinates must be odd. This can be fixed with the addition of a where clause to constrain the last character of the coordinate:

for $coord in distinct-values($xmldb/(tokenize(.,",")[1]))
where contains('13579bdf',substring($coord,string-length($coord)))
let $x := $xmldb[tokenize(.,",")[1] = $coord]
return <element xcoord="{$coord}">{count($x)}</element>