Xquery total

I am trying to write a xquery to get the total tuition paid by a  student.  the tuition per course is represented
by cost in my xml file below. My xml file and query are below:  Thanks

<StudentRecord xmlns:StudentRecord="http://www.studentRecord.com/">
  <Students>
        <student id="1111">
            <Name>John Brown</Name>
            <Course>Calculus</Course>
            <Cost>100.00</Cost>
      </student>
        <student id="2222">
            <Name>John Doer</Name>
            <Course>English</Course>
            <Cost>50.00</Cost>
        </student>
        <student id = "1111">
           <Name>John Brown</Name>
            <Course>CalculusII</Course>
            <Cost>150.00</Cost>
        </student>

    </Students>
</StudentRecord>

Here is my Xquery:
<solution>
{for $i in distinct-values(//Students/student/@id)
        let $tuition := 0
        return <student StudentID ="{$i}">
                {for $j in //Students/student[@id=$i]
                let $tuition := $tuition+$j/Cost
                return <tuition> {$tuition}</tuition>
                }
</student>
}
</solution>


Here is my result:
<solution>
  <student StudentID="1111">
    <tuition>100</tuition>
    <tuition>150</tuition>
  </student>
  <student StudentID="2222"><tuition>50</tuition></student>
</solution>

Here is what I'd like instead:
<solution>
  <student StudentID="1111">
    <tuition>250</tuition>
  </student>
  <student StudentID="2222"><tuition>50</tuition></student>
</solution>

nebestAsked:
Who is Participating?
 
jkmyoungCommented:
sorry, ignore my last comment: Was in a java state of mind.
You want something like:

let $tuition = sum(//Students/student[@id=$i]/Cost)

...
return <student StudentID ="{$i}">
           <tuition> {$tuition}</tuition>
         </student>

Forget the other for statement.
0
 
jkmyoungCommented:
               return <tuition> {$tuition}</tuition>
should be outside of the for $j statement.

Also, I think you're creating a new LOCAL variable $tuition each time you run
let $tuition := $tuition+$j/Cost

you'd need some sort of sum function or extension. Sorry that I'm not familiar enough with XQuery
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.