We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Xquery total

nebest
nebest asked
on
Medium Priority
428 Views
Last Modified: 2006-11-18
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>

Comment
Watch Question

Commented:
               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
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.