XQuery to group xml elements

0
votes

I was trying some grouping in xquery 

<Sheets>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>830</TPI>
    </Sheet>
    <Sheet>
        <ROI>5459</ROI>
        <TPI>830</TPI>
    </Sheet>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>5678</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>17473</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>857</TPI>
    </Sheet>
    <Sheet>
        <ROI>5713</ROI>
        <TPI>857</TPI>
    </Sheet>
</Sheets>

in my xquery, i want to pass a dynamic value say number 4... i want my xml to group to 4 Sheet elements to one set like

<Sheets>
    <set>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>830</TPI>
    </Sheet>
    <Sheet>
        <ROI>5459</ROI>
        <TPI>830</TPI>
    </Sheet>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>5678</ROI>
        <TPI>856</TPI>
    </Sheet>
    </set>
    <set>
    <Sheet>
        <ROI>17473</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>857</TPI>
    </Sheet>
    <Sheet>
        <ROI>5713</ROI>
        <TPI>857</TPI>
    </Sheet>
    </set>
</Sheets>

like, group 4 sheets to one set and other 4 to one set and finally rest of them to one set

2
xquery

2 Answers

1
votes

If you have XQuery 3.0 available, you could use a tumbling window to achieve this quite easily:

xquery version "3.0";

let $group-size := 4  (: set your group size here :)
return

    <Sheets>
    {
       for tumbling window $window in /Sheets/Sheet
       start at $start when fn:true()
       end at $end when $end - $start eq $group-size - 1
       return
           <set>{$window}</set>
    }   
    </Sheets>

Otherwise, if you only have XQuery 1.0 available to you, then you can do it with a simple FLWOR expression and positional predicates:

xquery version "1.0";

let $group-size := 4  (: set your group size here :)
return

    <Sheets>
    {
        for $group in (1 to round(count(/Sheets/Sheet) div $group-size) cast as xs:integer)
        let $start := (1 * ($group - 1) * $group-size) + 1
        let $end := $group * $group-size
        return
            <set>{/Sheets/Sheet[position() ge $start][position() le $end]}</set>
    }
    </Sheets>
0
votes

You could use subsequence() and call a function recursively.

Example...

XML Input

<Sheets>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>830</TPI>
    </Sheet>
    <Sheet>
        <ROI>5459</ROI>
        <TPI>830</TPI>
    </Sheet>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>5678</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>17473</ROI>
        <TPI>856</TPI>
    </Sheet>
    <Sheet>
        <ROI>5350</ROI>
        <TPI>857</TPI>
    </Sheet>
    <Sheet>
        <ROI>5713</ROI>
        <TPI>857</TPI>
    </Sheet>
</Sheets>

XQuery ($groupSize passed as 4)

declare variable $groupSize external;

declare function local:group($set as node()*, $size as xs:integer) as item()* {
    <set>{subsequence($set,1,$size)}</set>,
    if (count($set)>$groupSize) then
        local:group(subsequence($set,$groupSize+1),$groupSize)
    else
        ()
};

<Sheets>{local:group(/*/*,$groupSize)}</Sheets>

XML Output

<Sheets>
   <set>
      <Sheet>
        <ROI>5350</ROI>
        <TPI>830</TPI>
      </Sheet>
      <Sheet>
        <ROI>5459</ROI>
        <TPI>830</TPI>
      </Sheet>
      <Sheet>
        <ROI>5350</ROI>
        <TPI>856</TPI>
      </Sheet>
      <Sheet>
        <ROI>5678</ROI>
        <TPI>856</TPI>
      </Sheet>
   </set>
   <set>
      <Sheet>
        <ROI>17473</ROI>
        <TPI>856</TPI>
      </Sheet>
      <Sheet>
        <ROI>5350</ROI>
        <TPI>857</TPI>
      </Sheet>
      <Sheet>
        <ROI>5713</ROI>
        <TPI>857</TPI>
      </Sheet>
   </set>
</Sheets>