Returning an element and count of element for an xquery-CodePudding

I am new to xquery and I have been tasked with getting a list of how many section listings each instructor has been assigned.

So for example I'd want to return a list with the instructor and the count of course_listings.

I have tried something like this for the following xml.

for $x in doc(course.xml")/root/course_listing/section_listing
return $x//instructor//count(instructor_listing)

Any help would be appreciated.

<root>
<course_listing>
  <note>#</note>
  <course>216-088</course>
  <title>NEW STUDENT ORIENTATION</title>
  <credits>0</credits>
  <level>U</level>
  <restrictions>; ; REQUIRED OF ALL NEW STUDENTS. PREREQ: NONE</restrictions>
   <section_listing>
      <section_note></section_note>
      <section>Se 001</section>
      <days>W</days>
      <hours>
          <start>1:30pm</start>
          <end></end>
      </hours>
      <bldg_and_rm>
          <bldg>BUS</bldg>
          <rm>S230</rm>
      </bldg_and_rm>
      <instructor>Gusavac</instructor>
      <comments>9 WKS BEGINNING WEDNESDAY, 9/6/00 </comments>
   </section_listing>
   <section_listing>
      <section_note></section_note>
      <section>Se 002</section>
      <days>F</days>
      <hours>
          <start>11:30am</start>
          <end></end>
      </hours>
      <bldg_and_rm>
          <bldg>BUS</bldg>
          <rm>S171</rm>
      </bldg_and_rm>
      <instructor>Gusavac</instructor>
      <comments>9 WKS BEGINNING FRIDAY, 9/8/00 </comments>
   </section_listing>
</course_listing>

<course_listing>
  <note>#</note>
  <course>216-293</course>
  <title>BUSINESS ETHICS</title>
  <credits>3</credits>
  <level>U</level>
  <restrictions>; ; PREREQ: NONE</restrictions>
   <section_listing>
      <section_note></section_note>
      <section>Se 001</section>
      <days>R</days>
      <hours>
          <start>2:30pm</start>
          <end>5:10pm</end>
      </hours>
      <bldg_and_rm>
          <bldg>BUS</bldg>
          <rm>S230</rm>
      </bldg_and_rm>
      <instructor>Silberg</instructor>
   </section_listing>
</course_listing>
</root>

CodePudding user response：

Usually that is done by grouping:

declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";

declare option output:method 'text';
declare option output:item-separator '&#10;';


for $section in //section_listing
group by $instructor := $section/instructor
return $instructor || ': ' || count($section/parent::course_listing)