CODEWITHSUNDEEP
xmlxquery
User725
User725

Reputation: 19

XQuery with three separate XML files

While attempting to learn XQuery, I am trying to write a query that selects information from three two separate XML files. I have a little bit of experience with XQuery but I just can't think of a good way of structuring this kind of query. Here's the information in the each XML file.

Person.xml

<AllPersons>
  <Person>
    <Name>Jinchao Henson</Name>
    <ID>118784412</ID>
  </Person>
  <Person>
    <Name>Min Tuyet</Name>
    <ID>201586985</ID>
  </Person>
  <Person>
    <Name>John Basaraba</Name>
    <ID>124208644</ID>
  </Person>
  <Person>
    <Name>Richard Ellison</Name>
    <ID>997111094</ID>
  </Person>
  ...
</AllPersons>

Student.xml

<AllStudents>
  <Student>
    <StudentID>118784412</StudentID>
    <MentorID>201586985</MentorID>
  </Student>
  <Student>
    <StudentID>124208644</StudentID>
    <MentorID>997111094</MentorID>
  </Student>
  ...
</AllStudents>

Faculty.xml

<AllFaculty>
  <Faculty>
    <FacultyID>201586985</FacultyID>
    <Rank>Professor</Rank>
  </Faculty>
  <Faculty>
    <FacultyID>997111094</FacultyID>
    <Rank>Reader</Rank>
  </Faculty>
  ...
</AllFaculty>

A homework problem is asking to select each Student and their Mentor. I've attempted to write a query that loops through the Students, selecting the Student node, then two let expressions for the Person and Faculty nodes, then returning a Student's name and a Faculty's name but can't get it to work. I've also tried a query that loops through the Students, a let expression for Faculty, a where clause that matches StudentID to FacultyID and StudentID to Person/ID, then return that Person/Name and then loop through the faculty matching the ID and returning the Name.

Any help is much appreciated. Thanks.

Edit: Thanks for the advice. I Cleaned up XML examples and here is an attempt:

for $student in doc("../Student.xml")//Student
let $faculty := doc("../Faculty.xml")//Faculty
let $person := doc("../Person.xml")//Person
where $student/MentorID = $faculty/FacultyID and $student/StudentID = $person/ID
return
  <StudentMentor>{
      $person/Name,
      <Mentor>{
        for $personTwo in doc("../Person.xml")//Person
          where $personTwo/ID = $faculty/FacultyID
          return $personTwo/Name
      }</Mentor>
    }
 </StudentMentor> 
}

I'm pretty new to XQuery so I don't know the best way of structuring this query.

Edit 2: After looking at the data closer, I guess I don't even need the Faculty.xml file, I'll try to work on a better (actually working) solution.

Edit 3: Here is my working solution, please advise how I could make it more efficient.

for $student in doc("../Student.xml")//Student
let $personS := doc("../Person.xml")//Person[ID = $student/StudentID]
for $personM in doc("../Person.xml")//Person[ID = $student/MentorID]
return 
  <StudentMentor>
    <Student>{$personS/Name}</Student>,
    <Mentor>{$personM/Name}</Mentor>
  </StudentMentor>

Upvotes: 0

Views: 289

Answers (1)

Charles Duffy
Charles Duffy

Reputation: 295291

Better code might look a bit like this:

declare variable $students := doc("../Students.xml")/AllStudents/Student;
declare variable $faculty := doc("../Faculty.xml")/AllFaculty/Faculty;
declare variable $persons := doc("../Person.xml")/AllPersons/Person;

for $student in $students
let $faculty := $faculty[FacultyID=$student/MentorID]
let $student_person := $persons[ID=$student/StudentID]
let $faculty_person := $persons[ID=$student/MentorID]
return
  <StudentMentor>{
      $student_person/Name,
      <Mentor>{$faculty_person/Name}</Mentor>
    }</StudentMentor>

Using the above with your test data, I get the following output:

<StudentMentor>
  <Name>Jinchao Henson</Name>
  <Mentor>
    <Name>Min Tuyet</Name>
  </Mentor>
</StudentMentor>
<StudentMentor>
  <Name>John Basaraba</Name>
  <Mentor>
    <Name>Richard Ellison</Name>
  </Mentor>
</StudentMentor>

Note:

  • There aren't any where clauses here; your query doesn't need them, and selecting a huge set of possible permutations and using a where to winnow down is wildly inefficient (or at least, unidiomatic) as opposed to going straight for the data you actually want.
  • There's no point to selecting all students, or faculty, or persons inside a "let"; anything which will be global to the entire query, as opposed to scoped to the items iterated over with the "for", should be declared as a variable.
  • Replacing the use of // for a recursive search to instead go through the root node explicitly is more efficient if your database isn't indexed to be able to avoid that recursion. You could use $students_doc/*/Student (and likewise for the others) to similar effect.

Upvotes: 0

Related Questions