Search code examples
xslt

How to store all values of an element in a variable and later check this variable for a particular value in xslt

I have a xml file which have two elements 'employeeID' and 'managerID'. I like to check if managerID does not exist in any value in employeeID then replace the value of managerID with 'Not Found'.

I have to transform the below xml and replace the value of second element managerID with 'Not Found' if the value of managerID does not match with any value in employeeID.

<wd:Report_Data xmlns:wd="urn:com.workday.report/bsvc">
<wd:Report_Entry>
  <wd:employeeID>EMPLOYEE1</wd:employeeID>
  <wd:managerID>EMPLOYEE5</wd:managerID>
</wd:Report_Entry>
<wd:Report_Entry>
  <wd:employeeID>EMPLOYEE2</wd:employeeID>
  <wd:managerID>EMPLOYEE6</wd:managerID>
</wd:Report_Entry>
<wd:Report_Entry>
  <wd:employeeID>EMPLOYEE6</wd:employeeID>
  <wd:managerID>EMPLOYEE17</wd:managerID>
</wd:Report_Entry>
<wd:Report_Entry>
  <wd:employeeID>EMPLOYEE17</wd:employeeID>
  <wd:managerID>EMPLOYEE3</wd:managerID>
</wd:Report_Entry>
<wd:Report_Entry>
  <wd:employeeID>EMPLOYEE3</wd:employeeID>
  <wd:managerID>EMPLOYEE2</wd:managerID>
</wd:Report_Entry>
<wd:Report_Entry>
  <wd:employeeID>EMPLOYEE4</wd:employeeID>
  <wd:managerID>EMPLOYEE22</wd:managerID>
</wd:Report_Entry>
<wd:Report_Entry>
  <wd:employeeID>EMPLOYEE8</wd:employeeID>
  <wd:managerID>EMPLOYEE2</wd:managerID>
</wd:Report_Entry>
</wd:Report_Data>

My xslt is a below but this is returning 'Not found' for everyone. Expected out should be

EMPLOYEE1,Not Found

EMPLOYEE2,EMPLOYEE6

EMPLOYEE6,EMPLOYEE17

EMPLOYEE17,EMPLOYEE3

EMPLOYEE3,EMPLOYEE2

EMPLOYEE4,Not Found

EMPLOYEE8,EMPLOYEE2

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:wd="urn:com.workday.report/bsvc"
    xmlns:this="urn:this-stylesheet" exclude-result-prefixes="xs" version="2.0">
    
    <xsl:output method="text"/>
    
    <xsl:variable name="Delimiter" select="';'"/>
    <xsl:variable name="Newline" select="'&#xd;&#xa;'"/>
    
    <xsl:variable name="allemployeesID">
         <xsl:for-each select="/wd:Report_Data/Report_Entry">
            <xsl:value-of select="wd:employeeID"/>
        </xsl:for-each>
    </xsl:variable>
    
    <xsl:template match="/">
        <xsl:for-each select="wd:Report_Data/wd:Report_Entry">
            <xsl:value-of select="wd:employeeID"/>
            <xsl:value-of select="$Delimiter"/>
            <xsl:choose>
                <xsl:when test="contains($allemployeesID,wd:managerID)">
                    <xsl:value-of select="wd:managerID"/>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="'Not Found'" /> 
                </xsl:otherwise>
            </xsl:choose> 
            <xsl:value-of select="$Newline"/>
        </xsl:for-each> 
    </xsl:template>
</xsl:stylesheet>
Solution

  • Looks like just a typo: you are missing a wd namespace prefix in the XPath of the for-each loop in your allemployeesID definition.

        <xsl:variable name="allemployeesID">
         <xsl:for-each select="/wd:Report_Data/Report_Entry">
            <xsl:value-of select="wd:employeeID"/>
        </xsl:for-each>
    </xsl:variable>

    i.e. Report_Entry should be wd:Report_Entry

    Better: use a sequence variable instead of a string

    But can I suggest a simplification? Rather than having your variable contain a concatenation of the employee identifiers, and use the contains function to search that string, your variable could just be a sequence of the employee identifiers, which you could search using the = operator:

    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:wd="urn:com.workday.report/bsvc"
    xmlns:this="urn:this-stylesheet" exclude-result-prefixes="xs" version="2.0">
    
    <xsl:output method="text"/>
    
    <xsl:variable name="Delimiter" select="';'"/>
    <xsl:variable name="Newline" select="'&#xd;&#xa;'"/>
    
    <xsl:variable name="allemployeesID" 
      select="/wd:Report_Data/wd:Report_Entry/wd:employeeID"/>

    <xsl:template match="/">
        <xsl:for-each select="wd:Report_Data/wd:Report_Entry">
            <xsl:value-of select="wd:employeeID"/>
            <xsl:value-of select="$Delimiter"/>
            <xsl:choose>
                <xsl:when test="$allemployeesID = wd:managerID">
                    <xsl:value-of select="wd:managerID"/>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="'Not Found'" /> 
                </xsl:otherwise>
            </xsl:choose> 
            <xsl:value-of select="$Newline"/>
        </xsl:for-each> 
    </xsl:template>
</xsl:stylesheet>

    Better still, use a key

    NB in the example above, the @test expression $allemployeesID = wd:managerID is going to search the $allemployeesID sequence to find one which matches the wd:managerID, and that search will be a linear search. A better approach is to use a key, which will use an indexed search. If the input data file is large, and there are many employees, this may make a considerable difference to the runtime.

    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:wd="urn:com.workday.report/bsvc"
    xmlns:this="urn:this-stylesheet" exclude-result-prefixes="xs" version="2.0">
    
    <xsl:output method="text"/>
    
    <xsl:variable name="Delimiter" select="';'"/>
    <xsl:variable name="Newline" select="'&#xd;&#xa;'"/>
    <xsl:key name="allemployeesID"
      match="/wd:Report_Data/wd:Report_Entry/wd:employeeID"
      use="."
    />
    
    <xsl:template match="/">
        <xsl:for-each select="wd:Report_Data/wd:Report_Entry">
            <xsl:value-of select="wd:employeeID"/>
            <xsl:value-of select="$Delimiter"/>
            <xsl:choose>
                <xsl:when test="key('allemployeesID', wd:managerID)">
                    <xsl:value-of select="wd:managerID"/>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="'Not Found'" /> 
                </xsl:otherwise>
            </xsl:choose> 
            <xsl:value-of select="$Newline"/>
        </xsl:for-each> 
    </xsl:template>
</xsl:stylesheet>

    The xsl:key is used instead of the allemployeesID variable, and creates a search index, which you can invoke using the key() function.

        <xsl:key name="allemployeesID"
      match="/wd:Report_Data/wd:Report_Entry/wd:employeeID"
      use="."
    />
    • The name attribute provides a name for the search index.
    • The match attribute specifies which nodes will be indexed (all the wd:employeeID elements, in this case)
    • The use attribute specifies what you are looking them up by. In this case . means that you use the value of the wd:employeeID itself to look it up. Normally you'd look up something by a different value, such as looking up an element by the value of one of its attributes, but here you are just looking it up to find out if it exists.

    To use the key, invoke the key() function with parameters:

    • the name of the key
    • the value to look up (in this case, the value of a wd:employeeID

    The result of calling the key() function is the wd:employeeID element itself (or an empty sequence, if there was no wd:employeeID with that value).

    Consider moving logic to XPath to reduce xsl statements

    One final thing which is more of a stylistic comment. I personally find that in a job like this, which is effectively a traditional record-processing job, such as you might write in SQL or COBOL, it can be simpler to use fewer xsl elements but a more complex XPath expression. XPath's syntax is usually a lot more concise and readable, than the equivalent XSLT statements, so that can be worth doing when you aren't in need of XSLT's pattern-matching capabilities where you might need to use xsl:template. For example:

    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:wd="urn:com.workday.report/bsvc"
  xmlns:this="urn:this-stylesheet" exclude-result-prefixes="xs" version="2.0">

  <xsl:output method="text"/>

  <xsl:variable name="Delimiter" select="';'"/>
  <xsl:variable name="Newline" select="'&#xd;&#xa;'"/>
  <xsl:key name="allemployeesID"
    match="/wd:Report_Data/wd:Report_Entry/wd:employeeID"
    use="."
  />

  <xsl:template match="/">
      <xsl:value-of separator="{$Newline}" select="
        for $entry in wd:Report_Data/wd:Report_Entry return 
          concat(
            $entry/wd:employeeID,
            $Delimiter,
            if (key('allemployeesID', $entry/wd:managerID)) then
              $entry/wd:managerID
            else
              'Not Found'
          )
      "/>
  </xsl:template>
</xsl:stylesheet>