For the XML below, how would I find which Election, with the latest date if there are multiple elections elected, each employee has chosen. A default value of 'Bronze' would be used if all elections say 'Waive' or when there are no elections for that employee.
Example XML:
<Data>
<Employee>
<First_Name>Homer</First_Name>
<Last_Name>Simpson</<Last_Name>
<Elections>
<Election type="Homer Simpson on 01/01/2020 (Gold Membership) (Waive)"/>
</Elections>
<Elections>
<Election type="Homer Simpson on 01/01/2020 (Silver Membership) (Waive)"/>
</Elections>
</Employee>
<Employee>
<First_Name>Marge</First_Name>
<Last_Name>Simpson</<Last_Name>
<Elections>
<Election type="Marge Simpson on 01/01/2020 (Gold Membership) (Waive)"/>
</Elections>
<Elections>
<Election type="Marge Simpson on 01/03/2020 (Gold Membership) (Elect)"/>
</Elections>
<Elections>
<Election type="Marge Simpson on 01/03/2020 (Silver Membership) (Waive)"/>
</Elections>
</Employee>
<Employee>
<First_Name>Lisa</First_Name>
<Last_Name>Simpson</<Last_Name>
<Elections>
<Election type="Lisa Simpson on 01/01/2020 (Gold Membership) (Elect)"/>
</Elections>
<Elections>
<Election type="Lisa Simpson on 01/01/2020 (Silver Membership) (Waive)"/>
</Elections>
</Employee>
<Employee>
<First_Name>Bart</First_Name>
<Last_Name>Simpson</<Last_Name>
<Elections>
<Election type="Bart Simpson on 01/01/2020 (Gold Membership) (Waive)"/>
</Elections>
<Elections>
<Election type="Bart Simpson on 01/01/2020 (Silver Membership) (Waive)"/>
</Elections>
<Elections>
<Election type="Bart Simpson on 01/01/2020 (Bronze Membership) (Elect)"/>
</Elections>
</Employee>
<Employee>
<First_Name>Maggie</First_Name>
<Last_Name>Simpson</<Last_Name>
<Elections>
<Election type="Lisa Simpson on 01/01/2020 (Silver Membership) (Elect)"/>
</Elections>
<Elections>
<Election type="Lisa Simpson on 01/01/2020 (Gold Membership) (Waive)"/>
</Elections>
</Employee>
<Employee>
<First_Name>Grandpa</First_Name>
<Last_Name>Simpson</<Last_Name>
</Employee>
</Data>
I tried a few things such as a for-each over the Elections/Election tag looking for the string "(Gold Membership) (Elect)" and "(Silver Membership) (Elect)" and setting a variable if it was found but that didn't work.
The desired output would be:
Homer, Simpson, Bronze
Marge, Simpson, Gold
Lisa, Simpson, Gold
Bart, Simpson, Bronze
Grandpa, Simpson, Silver
This is a lot of work.
It would have been considerably simpler if each employee had only one election marked as Elect. But you say there can be more than one, and that only the latest one needs to be considered. And to make it even worse, you're using a date format that cannot be used for sorting as is.
Try something like the following:
XSLT 2.0
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/Data">
<xsl:for-each select="Employee">
<!-- names -->
<xsl:value-of select="First_Name" />
<xsl:text>,</xsl:text>
<xsl:value-of select="Last_Name" />
<xsl:text>,</xsl:text>
<!-- membership -->
<xsl:variable name="valid-elections" select="Elections/Election[contains(@type, '(Elect)')]"/>
<xsl:choose>
<xsl:when test="$valid-elections">
<xsl:variable name="memberships" as="element()*">
<xsl:perform-sort>
<!-- sort by reformatted date -->
<xsl:sort select="replace(@type, '.*(\d{2})/(\d{2})/(\d{4}).*', '$3$1$2')" order="descending"/>
<xsl:sequence select="$valid-elections"/>
</xsl:perform-sort>
</xsl:variable>
<!-- extract type from latest membership -->
<xsl:value-of select="replace($memberships[1]/@type, '.*\((Gold|Silver|Bronze) Membership\).*', '$1')" />
</xsl:when>
<xsl:otherwise>Bronze</xsl:otherwise>
</xsl:choose>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>