Merging XML nodes with identical child node values using XSLT

Question

My xml input is :

<?xml version="1.0" encoding="UTF-8"?>
<Root>
    <Row>
        <EmployeeID>21001</EmployeeID>
        <FMLAStartDate>2017-10-10</FMLAStartDate>
        <FMLAEndDate></FMLAEndDate>
        <FMLACorrectDate></FMLACorrectDate>
        <LTDStartDate></LTDStartDate>
        <LTDEndDate></LTDEndDate>       
        <LTDCorrectdate></LTDCorrectdate>
    </Row>

    <Row>
        <EmployeeID>21002</EmployeeID>
        <FMLAStartDate>2017-10-10</FMLAStartDate>
        <FMLAEndDate></FMLAEndDate>
        <FMLACorrectDate></FMLACorrectDate>
        <LTDStartDate></LTDStartDate>
        <LTDEndDate></LTDEndDate>       
        <LTDCorrectdate></LTDCorrectdate>
    </Row>

    <Row>
        <EmployeeID>21002</EmployeeID>
        <FMLAStartDate></FMLAStartDate>
        <FMLAEndDate></FMLAEndDate>
        <FMLACorrectDate></FMLACorrectDate>
        <LTDStartDate></LTDStartDate>
        <LTDEndDate>2017-10-08</LTDEndDate>     
        <LTDCorrectdate></LTDCorrectdate>
    </Row>

    <Row>
        <EmployeeID>21003</EmployeeID>
        <FMLAStartDate></FMLAStartDate>
        <FMLAEndDate></FMLAEndDate>
        <FMLACorrectDate></FMLACorrectDate>
        <LTDStartDate>2017-05-01</LTDStartDate>
        <LTDEndDate></LTDEndDate>       
        <LTDCorrectdate></LTDCorrectdate>
    </Row>

    <Row>
        <EmployeeID>21004</EmployeeID>
        <FMLAStartDate></FMLAStartDate>
        <FMLAEndDate></FMLAEndDate>
        <FMLACorrectDate>2017-01-10</FMLACorrectDate>
        <LTDStartDate></LTDStartDate>
        <LTDEndDate></LTDEndDate>       
        <LTDCorrectdate></LTDCorrectdate>
    </Row>
</Root>

Note that employee 21002 has two nodes, one holding the FMLA Start Date and other holding the LTD End date. Post XSLT transformation expectation is to have one row node per employee ID and this row node should hold the entire information for that employee ID. Hence expected output is:

<?xml version="1.0" encoding="UTF-8"?>
<Root>
   <Row>
    <EmployeeID>21001</EmployeeID>
    <FMLAStartDate>2017-10-10</FMLAStartDate>
    <FMLAEndDate></FMLAEndDate>
    <FMLACorrectDate></FMLACorrectDate>
    <LTDStartDate></LTDStartDate>
    <LTDEndDate></LTDEndDate>       
    <LTDCorrectdate></LTDCorrectdate>
   </Row>

   <Row>
    <EmployeeID>21002</EmployeeID>
    <FMLAStartDate>2017-10-10</FMLAStartDate>
    <FMLAEndDate></FMLAEndDate>
    <FMLACorrectDate></FMLACorrectDate>
    <LTDStartDate></LTDStartDate>
    <LTDEndDate>2017-10-08</LTDEndDate>     
    <LTDCorrectdate></LTDCorrectdate>
   </Row>

   <Row>
    <EmployeeID>21003</EmployeeID>
    <FMLAStartDate></FMLAStartDate>
    <FMLAEndDate></FMLAEndDate>
    <FMLACorrectDate></FMLACorrectDate>
    <LTDStartDate>2017-05-01</LTDStartDate>
    <LTDEndDate></LTDEndDate>       
    <LTDCorrectdate></LTDCorrectdate>
   </Row>

   <Row>
    <EmployeeID>21004</EmployeeID>
    <FMLAStartDate></FMLAStartDate>
    <FMLAEndDate></FMLAEndDate>
    <FMLACorrectDate>2017-01-10</FMLACorrectDate>
    <LTDStartDate></LTDStartDate>
    <LTDEndDate></LTDEndDate>       
    <LTDCorrectdate></LTDCorrectdate>
   </Row>
</Root>

I have tried for-each-group for employee ID based grouping but it doesn't give correct output. Can anyone please help me with this? I have basic knowledge of XSLT but haven't worked with advanced XSLT functionalities.

Thanks!

Answer 1

You can group the rows and concat all values for each field:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
  <xsl:template match="/Root">
    <xsl:for-each-group select="Row" group-by="EmployeeID">
      <Row>
        <EmployeeID>
          <xsl:value-of select="current-grouping-key()"/>
        </EmployeeID>
        <FMLAStartDate>
          <xsl:value-of select="current-group()/FMLAStartDate"/>
        </FMLAStartDate>
        <FMLAEndDate>
          <xsl:value-of select="current-group()/FMLAEndDate"/>
        </FMLAEndDate>
        <FMLACorrectDate>
          <xsl:value-of select="current-group()/FMLACorrectDate"/>
        </FMLACorrectDate>
        <LTDStartDate>
          <xsl:value-of select="current-group()/LTDStartDate"/>
        </LTDStartDate>
        <LTDEndDate>
          <xsl:value-of select="current-group()/LTDEndDate"/>
        </LTDEndDate>
        <LTDCorrectdate>
          <xsl:value-of select="current-group()/LTDCorrectdate"/>
        </LTDCorrectdate>
      </Row>
    </xsl:for-each-group>
  </xsl:template>
</xsl:stylesheet>

Answer 2

I would do

  <xsl:template match="/Root">
    <xsl:for-each-group select="Row" group-by="EmployeeID">
      <Row>
        <xsl:for-each-group select="current-group()/*" 
                            group-by="node-name()">
          <xsl:copy-of select="(current-group()[normalize-space()], 
                                current-group()[not(normalize-space()])[1]"/>
      </Row>
    </xsl:for-each-group>
  </xsl:template>

That is: for each group of employee records for the same employee ID, output a row, and for each element that is present in any or all of the employee elements in the group, output a copy of one of these, giving preference to those that have non-empty content.