How to select distinct values from XML file

Question

Hi,

I'am new in XML, and I Can't resolve this problem.

I have the following XML file

<?xml version="1.0" encoding="UTF-8"?>
-<Students> -<Student Id="001"> <Name>Poni</Name> <LastName>Mini</LastName> -<Courses> -<Course Id="01"> <Name>C#</Name> </Course> -<Course Id="02"> <Name>Java</Name> </Course> </Courses> </Student> -<Student Id="002"> <Name>Isa</Name> <LastName>Israelis</LastName> -<Courses> -<Course Id="02"> <Name>Java</Name> </Course> -<Course Id="03"> <Name>Oracle</Name> </Course> </Courses> </Student> -<Student Id="003"> <Name>Rafi</Name> <LastName>rafifa</LastName> -<Courses> -<Course Id="02"> <Name>Java</Name> </Course> -<Course Id="03"> <Name>Oracle</Name> </Course> </Courses> </Student> -<Student Id="004"> <Name>Yosi</Name> <LastName>Koen</LastName> -<Courses> -<Course Id="04"> <Name>SQL</Name> </Course> -<Course Id="03"> <Name>Oracle</Name> </Course> </Courses> </Student> </Students>

I want to select two something

1. All Courses
2. how many students studing in each cuerse

I know that I need to use XPATH maybe but i don't know how.

Can somebody help me please?

Thank you..

Answer 1

If you are using XPath 2.0 you can use:

distinct-values(//Course)

to get the Course list and then for each course get the Course Id and select all students in the particular course by using this XPath:

//Student[Courses/Course[@Id = %The Course Id Here%]]

If you are using XPath 1.0, Transform the XML Structure by using this XSLT, and then selecting Courses and Students should be straight forward:

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:key name="Courses" match="//Course" use="@Id" />
  <xsl:template match="Students">
    <Courses>
      <xsl:for-each select="//Course[generate-id(.)=generate-id(key('Courses',@Id))]">
        <xsl:sort select="@Id" order="ascending" />
        <Course Id="{@Id}">
          <Name>
            <xsl:value-of select="Name" />
          </Name>
          <Students>
            <xsl:variable name="Id" select="@Id" />
            <xsl:for-each select="//Student[Courses/Course[@Id = $Id]]">
              <Student Id="{@Id}">
                <Name>
                  <xsl:value-of select="Name" />
                </Name>
                <LastName>
                  <xsl:value-of select="LastName" />
                </LastName>
              </Student>
            </xsl:for-each>
          </Students>
        </Course>
      </xsl:for-each>
    </Courses>
  </xsl:template>
</xsl:stylesheet>

Morten la Cour