XQuery to group based on two variables

I have xml like this

<?xml version="1.0"?>
 <xmldb>
 <centers>
    <center id="0000100001" status="unsynced">
    <meetingdate>2011-01-13</meetingdate>
    <cname>Center1</cname>
    <cfe empid="1001">FE1</cfe>
    <groups>
        <group id="0000100002">
        <name>Group11</name>
         <members>
            <member id="0000100002">
                <fname>member111</fname>
                <lname>ln111</lname>
                    <loandetails>
                    <loan>
                        <productname>IGL1</productname>
                        <loanacctnum>0010000000001</loanacctnum>
                        <instnum>23</instnum>
                        <disbAmount>8000</disbAmount>
                        <disbDate>12/06/2010</disbDate>
                        <prdue>250</prdue>
                        <indue>25</indue>
                        <loanbalancePrincipal>2500</loanbalancePrincipal>
                        <loanbalanceIntrest>250</loanbalanceIntrest>
                        <amountpaid>0</amountpaid>
                        <attn>A</attn>
                    </loan>
                    <loan>
                        <productname>EDU</productname>
                        <loanacctnum>0010000000001</loanacctnum>
                        <instnum>23</instnum>
                        <disbAmount>2000</disbAmount>
                        <disbDate>12/06/2010</disbDate>
                        <prdue>110</prdue>
                        <indue>25</indue>
                        <loanbalancePrincipal>2500</loanbalancePrincipal>
                        <loanbalanceIntrest>250</loanbalanceIntrest>
                        <amountpaid>0</amountpaid>
                     </loan>
                    </loandetails>
            </member>
            <member id="0000100002">
                <fname>member111</fname>
                <lname>ln111</lname>
                    <loandetails>
                    <loan>
                        <productname>IGL1</productname>
                        <loanacctnum>0010000000001</loanacctnum>
                        <instnum>23</instnum>
                        <disbAmount>8000</disbAmount>
                        <disbDate>12/06/2010</disbDate>
                        <prdue>250</prdue>
                        <indue>25</indue>
                        <loanbalancePrincipal>2500</loanbalancePrincipal>
                        <loanbalanceIntrest>250</loanbalanceIntrest>
                        <amountpaid>0</amountpaid>
                    </loan> 
                    </loandetails>
            </member>
            <member id="0000100002">
                <fname>member112</fname>
                <lname>ln111</lname>
                    <loandetails>
                   开发者_如何学Go <loan>
                        <productname>IGL1</productname>

                        <loanacctnum>0010000000001</loanacctnum>
                        <instnum>23</instnum>
                        <disbAmount>8000</disbAmount>
                        <disbDate>12/06/2010</disbDate>
                        <prdue>250</prdue>
                        <indue>25</indue>
                        <loanbalancePrincipal>2500</loanbalancePrincipal>
                        <loanbalanceIntrest>250</loanbalanceIntrest>
                        <amountpaid>0</amountpaid>
                    </loan> 
                    </loandetails>
            </member>
            <member id="0000100003">
                <fname>member113</fname>
                <lname>ln111</lname>
                    <loandetails>
                        <loan>
                        <productname>IGL1</productname>
                        <loanacctnum>0010000000001</loanacctnum>
                        <instnum>23</instnum>
                        <disbAmount>8000</disbAmount>
                        <disbDate>12/06/2010</disbDate>
                        <prdue>250</prdue>
                        <indue>25</indue>
                        <loanbalancePrincipal>2500</loanbalancePrincipal>
                        <loanbalanceIntrest>250</loanbalanceIntrest>
                        <amountpaid>0</amountpaid>
                        </loan>
                    </loandetails>
            </member>
        </members>  
        </group>
        <group id="0000100003">
        <name>Group12</name>
        <members>
            <member id="0000100003">
                <fname>member113</fname>
                <lname>ln111</lname>
                    <loandetails>
                        <loan>
                        <productname>IGL1</productname>

                        <loanacctnum>0010000000001</loanacctnum>
                        <instnum>23</instnum>
                        <disbAmount>8000</disbAmount>
                        <disbDate>12/06/2010</disbDate>
                        <prdue>250</prdue>
                        <indue>25</indue>
                        <loanbalancePrincipal>2500</loanbalancePrincipal>
                        <loanbalanceIntrest>250</loanbalanceIntrest>
                        <amountpaid>0</amountpaid>
                        </loan>
                    </loandetails>
            </member>
        </members>  
        </group>
    </groups>   
    </center>
  </centers>
 </xmldb>

And need to aggregate data based on centeris and meeting date

So I query lie this

let $allItems := /xmldb/centers/center
for $d in distinct-values($allItems/@id)
for $n in distinct-values($allItems/meetingdate/text())
let $items := $allItems[@id = $d and meetingdate/text() = $n]
order by $items
return if (exists($items))
then <center id="{$d}" >
<date>{$n}</date>
<totaldue>{sum($items/groups/group/members/member/loandetails/loan/prdue)}</totaldue>
</center>
else ( )

which returs data like this

<center id="0000100003">
  <date>2011-01-11</date>
  <totaldue>610</totaldue>
</center>
<center id="0000100001">
  <date>2011-01-13</date>
  <totaldue>1360</totaldue>
</center>
<center id="0000100002">
  <date>2011-01-13</date>
  <totaldue>610</totaldue>
</center>

But I need data in this format

  <date value=2011-01-11>
     <center id="0000100003">
        <totaldue>610</totaldue>
     </center>
  </date>
  <date value=2011-01-13>
     <center id="0000100001">
        <totaldue>1360</totaldue>
     </center>
     <center id="0000100002">
        <totaldue>610</totaldue>
     </center>
  </date>

---

Try:

    let $allItems := /xmldb/centers/center
    for $date in distinct-values($allItems/meetingdate)
    return
      <date value="{$date}"> {  
        let $dateItems := $allItems[meetingdate = $date]
        for $id in distinct-values($dateItems/@id) 
        return <center id="{$id}" >
                  <totaldue>
                    {sum($dateItems[@id=$id]/groups/group/members/member/loandetails/loan/prdue)}
                  </totaldue>
               </center>
      }</date>

No need for that /text(), by the way - it's better to use the string value of the element than to find its text nodes, because there might be comments that get in the way.

I haven't tried sorting it, but it's easy enough to add "order by $date" or "order by $id" if that's what you need.

I think there's a tendency for people from a SQL background to try to do everything in one big multiway FLWOR join. Usually the output of XQuery is hierarchic rather than tabular, and you typically have one FLWOR expression (that is one return clause) for each layer of hierarchy in the output. In my experience, having more than one "for" clause in a single FLWOR expression is very unusual.