XQuery get data based on distinct values

1
votes

My xml is like this: full xml at inputxml

<course>
  <reg_num>10616</reg_num>
  <subj>BIOL</subj>
  <crse>361</crse>
  <sect>F</sect>
  <title>Genetics and MolecularBiology</title>
  <units>1.0</units>
  <instructor>Russell</instructor>
  <days>M-W-F</days>
  <time>
    <start_time>11:00AM</start_time>
    <end_time>11:50</end_time>
  </time>
  <place>
    <building>PSYCH</building>
    <room>105</room>
  </place>
</course>

I need to take distinct values for courses and return instructors that teach those courses.

This is my current code:

 let $x := doc("reed.xml")/root/course
 for $y in distinct-values($x/title)
 let    $z := $y/instructor
 return ( {data($y)} ,{data($z)})

What am i doing wrong

2
xqueryflwor

2 Answers

0
votes

In the code that you posted, the for loop is iterating over the sequence of distinct title string values.

You can't XPath into those strings.

Rather, you want to XPath into the $x courses and use the $y in each iteration to select those course elements that have the title equal to $y, then select it's instructor.

let $x := doc("reed.xml")/root/course
for $y in distinct-values($x/title)
let $z := $x[title = $y]/instructor
return ( {data($y)} ,{data($z)})
0
votes

In XQuery 3.1 you can do this with group by.

for $course := doc("reed.xml")/root/course
group by $title := $course/title
return ( <title>{$title}</title> , 
         <instructors>{$course/instructor}</instructors> )