xquery -nested for with variable

Question

i have this xml

<Edu>
<Department>
 <Student>
  <name>jack</name>
  <Std_ID>12345678</Std_ID>
  <Degree>MS</Degree>
   </Student>
   <Student>
  <name>melin</name>
  <Std_ID>56784921</Std_ID>
  <Degree>PHD</Degree>
   </Student>

    <Student>
  <name>sali</name>
  <Std_ID>54673821</Std_ID>
  <Degree>MS</Degree>
   </Student>
   <Student>
  <name>parisa</name>
  <Std_id>54321876</Std_id>
  <Degree>BS</Degree>
  </Student> 

  <Student>
   <name>caty</name>
   <Std_ID>87654321</Std_ID>
   <Degree>MS</Degree>
  </Student> 


</Department>
<Course>
<name>programing</name>
<Student>
<name>jack</name>
<Std_ID>12345678</Std_ID>
<Degree>MS</Degree>
 </Student>
 <Student>
  <name>sali</name>
  <Std_ID>54673821</Std_ID>
  <Degree>MS</Degree>
   </Student>

<Student>
<name>parisa</name>
<Std_id>54321876</Std_id>
<Degree>BS</Degree>
</Student> 
</Course>
<Course>
<name>network</name>
 <Student>
 <name>jack</name>
 <Std_ID>12345678</Std_ID>
 <Degree>MS</Degree>
 </Student>
 </Course>
 <Course>
 <name>Database</name>

 <Student>
  <name>jack</name>
  <Std_ID>12345678</Std_ID>
  <Degree>MS</Degree>
 </Student>

 <Student>
  <name>caty</name>
  <Std_ID>87654321</Std_ID>
  <Degree>MS</Degree>
 </Student> 
 </Course>
  <Course>

  <Student>
  <name>jack</name>
  <Std_ID>12345678</Std_ID>
  <Degree>MS</Degree>
   </Student>

  <Student>
   <name>caty</name>
   <Std_ID>87654321</Std_ID>
   <Degree>MS</Degree>
  </Student>   
  </Course>
  </Edu>

and i want to list student with MS Degree that has more than 2 course,or BS Student that has more than one course. i means that i want name of student with ms degree that has more than 2 course or student with bs degree that has more than one course.i want both of these list. this is my xquery: but it does not work:

for $i in doc("XMLFile_Q2.xml")/Edu/Department/Student[Degree="MS" or Degree="BS"]
let $counter :=0
for $a in doc("XMLFile_Q2.xml")/Edu/Course
where $a/Student[name]=$i[name] 
 let $counter := $counter+1
  return if($a/Student[Degree]="MS" and $counter gt 2 )
    then  $a/Student/name
    else if($a/Student[Degree]="BS" and $counter gt 1)
    then $a/Student/name/text()
    else()

Answer 1

First and most important: your code (or at least large parts of it) would be perfectly normal in an imperative language, but XQuery is a functional language. You don't count the number of courses in which jack or melin are enrolled by iterating through a list and incrementing a counter. In a functional language, variables are not mutable. You create an expression which, when evaluated, produces the answer you want. Think declaratively.

Second, learn that predicates (parts of an XPath expression which are enclosed within square brackets) are not accessors for fields in a record or members in an object. Don't write $a/Student[name] where you mean $a/Student/name.

Third, work on your syntax: in a FLWOR expression, the where clause can't be followed by another let clause. The for and let clauses together built up a tuple of name/value pairs, which the where clause then filters.

You want a list of all MS and BS students who fulfil certain criteria. Start by making a list of MS and BS students:

for $student in doc(...)/Edu/Department
                /Student[Degree = ('MS', 'BS')]
return $student/name

Next, you want to filter that list to include MS students with more than two courses or BS students with more than one, and exclude the others. So you want something that looks roughly like this:

for $student in doc(...)/Edu/Department
                /Student[Degree = ('MS', 'BS')]
...
where ($student/Degree = 'MS' and count($courses) gt 2)
      or 
      ($student/Degree = 'BS' and count($courses) gt 1)
return $student/name (: or perhaps just $student :)

So now where the skeleton has a ... we need to bind the variable $courses to the set of courses for which the current $student is signed up. The following let expression will do it:

let $course := doc(...)/Edu/Course[Student/name = $student/name]

The resulting query is a little clumsy and repetitive, so in real life I'd factor out more of the common expressions and produce something like this:

for $student in $doc/Department/Student
let $degree := $student/Degree/string(),
    $name := $student/name,
    $courses := $doc/Course[Student/name = $name],
    $coursecount := count($courses)
where ($degree = 'MS' and $coursecount gt 2)
      or 
      ($degree = 'BS' and $coursecount gt 1)
return <student name="{$name}" 
                degree="{$degree}" 
                courses="{$coursecount}"/>