CODEWITHSUNDEEP
xmlxquery
riya
riya

Reputation: 51

generate xquery using given output

I want to generate xquery using given output. My XML is as follows:

   <?xml version="1.0" encoding="UTF-8"?>

   <S_db>
    <student id="94811">
    <name>Rusty Nail</name>
    <quizzes>
        <quiz>16</quiz>
        <quiz>12</quiz>
    </quizzes>
    <projects>
        <project>44</project>
        <project>52</project>
    </projects>
    <exams>
        <exam>77</exam>
        <exam>68</exam>
        <exam>49</exam>
        </exams>
    </student>
    <student id="2562">
    <name>Guy Wire</name>
    <quizzes>
        <quiz>15</quiz>
        <quiz>23</quiz>
    </quizzes>
    <projects>
        <project>33</project>
        <project>47</project>
        </projects>
    <exams>
        <exam>78</exam>
        <exam>86</exam>
        <exam>88</exam>
    </exams>
    </student>
    <student id="137745">
    <name>Barb Wire</name>
    <quizzes>
        <quiz>20</quiz>
        <quiz>25</quiz>
    </quizzes>
    <projects>
        <project>48</project>
        <project>60</project>
        </projects>
    <exams>
        <exam>38</exam>
        <exam>48</exam>
        <exam>66</exam>
    </exams>
    </student>
    </S_db>

Output to be generated is as follows:

<?xml version="1.0" encoding="UTF‑8"?>
<projects>
<student>
<name>Barb Wire</name>
<total>108</total>
</student>
<student>
<name>Guy Wire</name>
<total>80</total>
</student>
<student>
<name>Rusty Nail</name>
<total>96</total>
</student>

I tried to generate Xquery as follows:

    <student>
   {for $i in (1 to 3) return
   <total> { for $a in /S_db
   let $s := sum($a/student[$i]/projects/project)
   let $name := $a/student[$i]/name
   return ($s)}
   </total>
   }
  </student>

My Current Output:

<?xml version="1.0" encoding="UTF-8"?>
<student>
<total>96</total>
<total>80</total>
<total>108</total>
</student>

I don't know how to get that name tag with corresponding total tag. I have stored the name element of particular student in name variable

Upvotes: 2

Views: 71

Answers (1)

har07
har07

Reputation: 89325

This is one possible way :

<projects>
{
    for $s in /S_db/student
    return 
    <student>
    {
        $s/name,
        <total>{sum($s/projects/project)}</total>
    }
    </student>
}
</projects>

xpathtester.com demo

output :

<?xml version="1.0" encoding="UTF-8"?>
<projects>
   <student>
      <name>Rusty Nail</name>
      <total>96</total>
   </student>
   <student>
      <name>Guy Wire</name>
      <total>80</total>
   </student>
   <student>
      <name>Barb Wire</name>
      <total>108</total>
   </student>
</projects>

Upvotes: 1

Related Questions