CODEWITHSUNDEEP
xmlxquerydistinct-values
jj123456
jj123456

Reputation: 669

XQuery: select distinct nodes

I am trying to learn XQuery and I haven't figured out how to select distinct teachers in the following xml file that I have made. I have looked up distinct-values() but it only seems to return a sequence of Strings only. If I want to run XQuery only without declaring functions such as functx:distinct-deep and functx:distinct-nodes from http://www.xqueryfunctions.com/xq/c0015.html#c0053, is there a way I can select distinct teachers who have taught in both credit and non-credit courses in the same year from the xml file below? I'd appreciate your help. Thank you.

<DB>
<credit>
<course_title>biology101</course_title>
    <teacher>
        <lastname>Smith</lastname>
        <firstname>Amy</firstname>
    </teacher>
    <teacher>
        <lastname>Hanson</lastname>
        <firstname>John</firstname>
    </teacher>
    <teacher>
        <lastname>Lee</lastname>
        <firstname>Sandra</firstname>
    </teacher>
    <year>2013</year>
</credit>
<credit>
<course_title>chemistry101</course_title>
    <teacher>
        <lastname>Richardson</lastname>
        <firstname>Rob</firstname>
    </teacher>
    <teacher>
        <lastname>Hanson</lastname>
        <firstname>John</firstname>
    </teacher>
    <year>2013</year>
</credit>
<noncredit>
<course_title>physics101</course_title>
    <teacher>
        <lastname>Hanson</lastname>
        <firstname>John</firstname>
    </teacher>
    <teacher>
        <lastname>Head</lastname>
        <firstname>Peter</firstname>
    </teacher>
    <year>2013</year>
</noncredit>
<noncredit>
<course_title>physics101</course_title>
    <teacher>
        <lastname>Lee</lastname>
        <firstname>Sandra</firstname>
    </teacher>
    <teacher>
        <lastname>Head</lastname>
        <firstname>Peter</firstname>
    </teacher>
    <teacher>
        <lastname>Hanson</lastname>
        <firstname>John</firstname>
    </teacher>
    <year>2013</year>
</noncredit>
</DB>

I used the XQuery below:

let $DB := doc("myDB.xml") /DB
let $credit_teacher := $DB/credit/teacher

for $noncredit_teacher in $DB/noncredit/teacher
where $DB/credit/year = $DB/noncredit/year
and $credit_teacher/lastname = $noncredit_teacher/lastname
and $credit_teacher/firstname = $noncredit_teacher/firstname
return $noncredit_teacher

But the duplicating teachers are returned:

<teacher>
  <lastname>Hanson</lastname>
  <firstname>John</firstname>
</teacher>
<teacher>
  <lastname>Lee</lastname>
  <firstname>Sandra</firstname>
</teacher>
<teacher>
  <lastname>Hanson</lastname>
  <firstname>John</firstname>
</teacher>

If I use distinct-values, a sequence of Strings is returned: HansonJohn LeeSandra

Upvotes: 1

Views: 1328

Answers (2)

adamretter
adamretter

Reputation: 3517

One possible solution is to:

  1. Find all the teachers that have taught in both credit and non-credit modules within the same year.

  2. Filter those teachers so that they are distinct. I have used a tail-recursive function local:distinct-teachers to achieve this, that operates over the sequence of all teachers found in (1). It examines each teacher in turn and if it has not see it before, it adds it to an accumulator of $distinct teachers.

    declare function local:distinct-teachers($teachers as element(teacher)*, $distinct as element(teacher)*) as element(teacher)* {
  if(empty($teachers)) then
    $distinct
  else
    let $teacher := $teachers[1]
    return
      let $new-distinct := if($distinct/.[lastname eq $teacher/lastname]
          [firstname eq $teacher/firstname]
      ) then
          $distinct
      else
          ($teacher, $distinct)
      return
          local:distinct-teachers($teachers[position() gt 1], $new-distinct)
};


let $teachers :=
    let $credit-teachers := doc("myDB.xml")/DB/credit/teacher
    let $non-credit-teachers := doc("myDB.xml")/DB/noncredit/teacher
    return
        for $credit-teacher in $credit-teachers
        for $non-credit-teacher in $non-credit-teachers
        return
            $credit-teacher
                [lastname eq $non-credit-teacher/lastname]
                [firstname eq $non-credit-teacher/firstname]
                [following-sibling::year eq $non-credit-teacher/following-sibling::year]
return
    local:distinct-teachers($teachers, ())

Upvotes: 1

joemfb
joemfb

Reputation: 3056

You are already selecting the distinct nodes. The teacher nodes you are selecting are repeated in the source XML, and therefore have separate node identities.

You'll have to do some form of de-duplication based on the contents of the nodes. functx:distinct-nodes() and functx:distinct-deep are good examples of that kind of de-duplication.

Upvotes: 0

Related Questions