how to write this query for this xml data in linq to xml?

Question

i've been reading through the linq to xml documentation in msdn and some other tutorials and yet i failed to find the right way to do this query as of now :(

Basically, i want to loop through each student in the class, for each question, keep a counter of individual skill, i.e. for student 123, the tally should be 001.101.033.002.001 - 1, 001.101.035.002.001 - 1, 001.101.033.002.002 - 0. as the skills counter is based on whether the question is right(1) or wrong(0).

<assessment name="english101" level="primary 6">
   <class id="23" name="1A">
      <student id="123" name="Jack Black">
         <question id="101" correct="1">
            <skill id="001.101.033.002.001" topicId="033" subtopicId="002" subtopicdesc="Launching a browser" topicdesc="Point and Click">Able to recognize and use desktop icon</skill>

            <skill id="001.101.035.002.001" topicId="035" topicDesc="Typing" subtopicId="002" subtopicDesc="Using Words">Able to write on screen</skill>
         </question>

         <question id="102" correct="0">
            <skill id="001.101.033.002.002" topicId="033" subtopicId="002" subtopicdesc="Launching a browser" topicdesc="Point and Click">Able to recognize and use the mouse</skill>

            <skill id="001.101.035.002.001" topicId="035" topicDesc="Typing" subtopicId="002" subtopicDesc="Using Words">Able to write on screen</skill>
         </question>
      </student>

      <student id="124" name="Tim Robbins">
         <question id="103" correct="1">
            <skill id="001.101.033.002.002" topicId="033" subtopicId="002" subtopicdesc="Launching a browser" topicdesc="Point and Click">Able to recognize and use the mouse</skill>

            <skill id="001.101.035.002.001" topicId="035" topicDesc="Typing" subtopicId="002" subtopicDesc="Using Words">Able to write on screen</skill>
         </question>

         <dtasResult>
            <skill id="001.101.033.002.002" result="weak" />

            <skill id="001.101.033.002.002" result="strong" />
         </dtasResult>
      </student>
   </class>
</assessment>

so far the code i have is here:

    //Open up the xml and traverse to the student nodes.
                    XElement root = XElement.Load(fileLocation);
                    IEnumerable<XElement> students = root.Elements("class").Elements("student");

                    string currentStudentId = "";
                    foreach (XElement student in students)
                    {
                        currentStudentId = student.Attribute("id").ToString();
                        //this code chunk below doesn't work!
XElement questions = root.Descendants("question")
                                                .Where(question =>
                                                question.Attribute(""));

                        Console.WriteLine(student.DescendantsAndSelf("question"));
                        Console.WriteLine();
                        Console.WriteLine();
                    }

i have yet to figure out how to do the looping for each question per student.. and the code in the middle of the foraech doesn't work!!

Updates:

if i wanted to calculate the total question per student, how would i be able to modify your query above to do so? i try grp.Count(z => z.Correct) and other method but i can't get it right thanks :)

Answer 1

Second edit re comment; I think this does what you want...

var qry = from cls in doc.Root.Elements("class")
          from student in cls.Elements("student")
          from question in student.Elements("question")
          from skill in question.Elements("skill")
          select new {
            Student = (int)student.Attribute("id"),
            Skill = (string)skill.Attribute("id"),
            Correct = (int)question.Attribute("correct")
          } into denorm
          group denorm by new {
            denorm.Student,
            denorm.Skill
          } into grp
          orderby grp.Key.Student, grp.Key.Skill
          select new {
            grp.Key.Student,
            grp.Key.Skill,
            Tally = grp.Sum(x => x.Correct)
          };

foreach (var row in qry)
{
  Console.WriteLine("{0}\t{1}\t{2}",
    row.Student, row.Skill, row.Tally);
}

Answer 2

Not exactly sure if this is what you want, but I'll give it a try:

foreach (XElement student in students)
        {
            currentStudentId = student.Attribute("id").ToString();
            Console.WriteLine(currentStudentId);
            //this code chunk below doesn't work!
            IEnumerable<XElement> correctQuestions = student.Descendants("question").
                                  Where(q => q.Attribute("correct").Value == "1");                
            //Here you would do same for false answers

            //loop through correct answers
            foreach (XElement q in correctQuestions)
            {
                IEnumerable<XElement> skills = q.Descendants("skill");
                foreach (XElement s in skills)
                {
                    //this prints "001.101.035.002.001" etc  
                    Console.WriteLine(s.Attribute("id").Value);
                }
            }
            Console.WriteLine();
        }