Using if statements on two XML documents

Question

I have two XML docs Students.xml and Admission.xml, I need a xquery to return the Student ID and Student Name of students who have completed 30 days from the admission date. I am confused on how to join them and to use a condition to come up with,please help.

The Student.xml

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<StudentDetails>
    <Student_ID>1</Student_ID>
    <Student_Name>Mark</Student_Name>
</StudentDetails>
<StudentDetails>
    <Student_ID>2</Student_ID>
    <Student_Name>Robin</Student_Name>
</StudentDetails>
<StudentDetails>
    <Student_ID>3</Student_ID>
    <Student_Name>Charlie</Student_Name>
</StudentDetails>
</root>

The Admission.xml

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<AdmissionDetails>
    <Admission_ID>1</Admission_ID>
    <Admission_Date>2017-10-06</Admission_Date>
    <Admission_Status>Admitted</Admission_Status>
    <Student_ID>3</Student_ID>
</AdmissionDetails>
<AdmissionDetails>
    <Admission_ID>2</Admission_ID>
    <Admission_Date>2017-04-14</Admission_Date>
    <Admission_Status>Admitted</Admission_Status>
    <Student_ID>2</Student_ID>
</AdmissionDetails>
<AdmissionDetails>
    <Admission_ID>3</Admission_ID>
    <Admission_Date>2017-08-16</Admission_Date>
    <Admission_Status>Admitted</Admission_Status>
    <Student_ID>1</Student_ID>
</AdmissionDetails>

My code so far is this but it gives me a result which is a repetition of values, need help desperately.

for $sid in doc("Student.xml")/root/StudentDetails/Student_ID
for $aid  in doc("Admission.xml")/root/AdmissionDetails/Student_ID
for $x in doc("Admission,xml")/root/AdmissionDetails/Admission_Date
where $sid = $aid and $x >current-date() + xs:dayTimeDuration("P30D")
return  $sid

And this is the result

<?xml version="1.0" encoding="UTF-8"?>
<Student_ID>1</Student_ID>
<Student_ID>1</Student_ID>
<Student_ID>10</Student_ID>
<Student_ID>10</Student_ID>

Any reference to existing blogs or answers would also help much, thanks again — Abraham, May 01 '18 at 22:22

score 1 · Accepted Answer · answered May 02 '18 at 07:42

1

I think you're falling into the trap of thinking that every query has to be a FLWOR expression (it's generally a sign that you've spent too long writing SQL).

I would write this as:

    let $S30 := doc("Admission.xml")/*/Admission_Details
        [xs:date(Admission_Date) < current-date() + xs:dayTimeDuration("P30D")]
        /Admission_ID
    return doc("Student.xml")/*/StudentDetails[Student_ID = $S30]

answered May 02 '18 at 07:42

Michael Kay

156,231
11
92
164

Hi Michael, You are right, i have spent too long writing SQL and your code worked, thank you very much – Abraham May 02 '18 at 21:29

score 0 · Answer 2 · answered May 02 '18 at 03:25

You could iterate over each of the StudentDetails in the Students.xml documents, and then filter WHERE the StudentDetails/Student_ID value is equal to an Admission.xml AdmissionDetails element's Student_ID and has an Admission_Date that meets your criteria (are you sure that is the correct date filter? Your description says Completion Date within 30 days of Admission, but the sample data does not have a Completion Date, only the Admission Date), and then return the Student_ID and Student_Name from those students that satisfy the WHERE clause conditions.

for $student in doc("Student.xml")/root/StudentDetails
where doc("Admission.xml")/root/AdmissionDetails[
        Student_ID = $student/Student_ID and 
        xs:date(Admission_Date) < current-date() + xs:dayTimeDuration("P30D") 
      ]
return 
  ($student/Student_ID, $student/Student_Name)

The XQuery specification has examples of "inner" and "outer" joins that might be informative.

Using if statements on two XML documents

2 Answers2