Converting Flat XML to Nested XML using XSLT v2.0-CodePudding

I am trying to create a Nested XML from Simple XML. But not able to figure out how to group and distinct the identical elements. Also, how can I add second level of nesting in XSLT using for-each-group.

Below is my input XML:

<?xml version="1.0" encoding="UTF-8"?>
<!--XML document created Oct 28, 2022 9:43:47 AM-->
<dataroot>
  <CTMS_Simple>
    <EmployeeID>EmployeeID 1</EmployeeID>
    <DEPT_REFERENCE>DEPT 1</DEPT_REFERENCE>
    <DEPT_CODE>DEPTCC 1</DEPT_CODE>
    <DEPT_NAME>DEPTDN 1</DEPT_NAME>
    <LocationID>Location 1</LocationID>
  </CTMS_Simple>
  <CTMS_Simple>
    <EmployeeID>EmployeeID 1</EmployeeID>
    <DEPT_REFERENCE>DEPT 1</DEPT_REFERENCE>
    <DEPT_CODE>DEPTCC 1</DEPT_CODE>
    <DEPT_NAME>DEPTDN 1</DEPT_NAME>
    <LocationID>Location 2</LocationID>
  </CTMS_Simple>
  <CTMS_Simple>
    <EmployeeID>EmployeeID 2</EmployeeID>
    <DEPT_REFERENCE>DEPT 1</DEPT_REFERENCE>
    <DEPT_CODE>DEPTCC 1</DEPT_CODE>
    <DEPT_NAME>DEPTDN 3</DEPT_NAME>
    <LocationID>Location 1</LocationID>
  </CTMS_Simple>
  <CTMS_Simple>
    <EmployeeID>EmployeeID 3</EmployeeID>
    <DEPT_REFERENCE>DEPT 2</DEPT_REFERENCE>
    <DEPT_CODE>DEPTCC 2</DEPT_CODE>
    <DEPT_NAME>DEPTDN 2</DEPT_NAME>
    <LocationID>Location 4</LocationID>
  </CTMS_Simple>
</dataroot>

I created below XSLT v2.0 file: I am not able to include Location data in stylesheet.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs xsi xsl">

    <xsl:template match="/">
        <xsl:for-each-group select="/dataroot/CTMS_Simple"
            group-by="DEPT_REFERENCE">
            <Department>
                <DEPTID><xsl:value-of select="DEPT_REFERENCE" /></DEPTID>
                <DEPTCODE><xsl:value-of select="DEPT_CODE" /></DEPTCODE>
                <DeptName><xsl:value-of select="DEVELOPMENT_NAME" /></DeptName>
                <xsl:for-each select="/dataroot/CTMS_Simple/EmployeeID[../DEPT_REFERENCE=current-grouping-key()]">
                    <Employee>
                                             <EmployeeID>
                        <xsl:value-of select="." />
                                             </EmployeeID>
                    </Employee>
                </xsl:for-each>
            </Department>
        </xsl:for-each-group>

    </xsl:template>
</xsl:stylesheet>

I get below output:

<Department>
   <DEPTID>DEPT 1</DEPTID>
   <DEPTCODE>DEPTCC 1</DEPTCODE>
   <DeptName/>
   <Employee>
      <EmployeeID>EmployeeID 1</EmployeeID>
   </Employee>
   <Employee>
      <EmployeeID>EmployeeID 1</EmployeeID>
   </Employee>
   <Employee>
      <EmployeeID>EmployeeID 2</EmployeeID>
   </Employee>
</Department>
<Department>
   <DEPTID>DEPT 2</DEPTID>
   <DEPTCODE>DEPTCC 2</DEPTCODE>
   <DeptName/>
   <Employee>
      <EmployeeID>EmployeeID 3</EmployeeID>
   </Employee>
</Department>

But I need output as below after also adding Location data.

<Department>
   <DEPTID>DEPT 1</DEPTID>
   <DEPTCODE>DEPTCC 1</DEPTCODE>
   <DeptName/>
   <Employee>
      <EmployeeID>EmployeeID 1</EmployeeID>
<Location>
    <LocationID>Location 1</LocationID>
    <LocationID>Location 2</LocationID>
</Location>
   </Employee>
   <Employee>
      <EmployeeID>EmployeeID 2</EmployeeID>
<Location>
    <LocationID>Location 1</LocationID>
</Location>
   </Employee>
</Department>
<Department>
   <DEPTID>DEPT 2</DEPTID>
   <DEPTCODE>DEPTCC 2</DEPTCODE>
   <DeptName/>
   <Employee>
      <EmployeeID>EmployeeID 3</EmployeeID>
<Location>
    <LocationID>Location 4</LocationID>
</Location>
   </Employee>
</Department>

CodePudding user response：

I guess you want something like:

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/dataroot">
    <xsl:for-each-group select="CTMS_Simple" group-by="DEPT_REFERENCE">
        <Department>
            <DEPTID>
                <xsl:value-of select="DEPT_REFERENCE" />
            </DEPTID>
            <DEPTCODE>
                <xsl:value-of select="DEPT_CODE" />
            </DEPTCODE>
            <DeptName>
                <xsl:value-of select="DEVELOPMENT_NAME" />
            </DeptName>
            <xsl:for-each-group select="current-group()" group-by="EmployeeID">
                <Employee>
                    <EmployeeID>
                        <xsl:value-of select="EmployeeID" />
                    </EmployeeID>
                    <Location>
                        <xsl:for-each select="current-group()">
                            <LocationID>
                                <xsl:value-of select="LocationID" />
                            </LocationID>
                        </xsl:for-each>
                    </Location>
                </Employee>
            </xsl:for-each-group>
        </Department>
    </xsl:for-each-group>
</xsl:template>

</xsl:stylesheet>

Note that the result is an XML fragment, not a well-formed XML document (does not have a single root element).