DEVHIDE
Login Or Sign up

XSLT 2.0 or XSLT3.0: Group and Merge nodes together based on same value

80 Views Asked by At

I have been having trouble getting desired output while transforming below xml. I had posted a couple of similar questions in the last few days. The solution that I received gets the desired output for the source XML that I provided in those questions. However, my source XML has slightly changed since I posted my last question and here is the final version of the source XML.

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<Workers>
    <Worker>
        <UniqueID>67896</UniqueID>
        <ExpenseAmount>400.00</ExpenseAmount>
        <BonusAmount>230.00</BonusAmount>
    </Worker>
    <Worker>
        <UniqueID>12465</UniqueID>
        <ExpenseAmount>500.00</ExpenseAmount>
        <BonusAmount>430.00</BonusAmount>
    </Worker>
    <Worker>
        <UniqueID>71354</UniqueID>
        <ExpenseAmount>998.00</ExpenseAmount>
        <Misc>
            <Date>2023-10-10-07:00</Date>
            <BonusAmount>400.00</BonusAmount>
        </Misc>
        <Misc>
            <Date>2023-10-10-07:00</Date>
            <BonusAmount>520.00</BonusAmount>
        </Misc>
        <Misc>
            <Date>2023-10-09-07:00</Date>
            <BonusAmount>900.00</BonusAmount>
        </Misc>
    </Worker>
    <Worker>
        <UniqueID>45679</UniqueID>
        <ExpenseAmount>1507.00</ExpenseAmount>
        <Misc>
            <Date>2023-10-06-07:00</Date>
            <BonusAmount>322.00</BonusAmount>
        </Misc>
        <Misc>
            <Date>2023-10-08-07:00</Date>
            <BonusAmount>800.00</BonusAmount>
        </Misc>
        <Misc>
            <Date>2023-10-09-07:00</Date>
            <BonusAmount>410.00</BonusAmount>
        </Misc>
        <Misc>
            <Date>2023-10-10-07:00</Date>
            <BonusAmount>600.00</BonusAmount>
        </Misc>
    </Worker>
</Workers>

Requirement

  • In source XML, the value for element <UniqueID> under a <Worker> node will not be repeated in the source xml

  • There are 4 different dates in the whole document, such as 2023-10-10-07:00 2023-10-09-07:00 2023-10-08-07:00 2023-10-06-07:00. Since my final output should be grouped based on <Date> and there are 4 different dates on my source xml, my expected result should have 4 <EmployeeRecords>nodes and each <Employee> node should hold unique <Date> element corresponding to <Worker>

  • In source XML, <Worker>nodes may or may not contain <Misc> nodes. Any Worker node that doesn't have Misc node should be copied under all EmployeeRecords nodes in the expected output

  • In source XML, <Misc> nodes under a specific <Worker> node may have different <Date> and <BonusAmount> from other Misc nodes under that specific Worker node.

  • In source XML, it is possible to have more than one Misc nodes with same Date under Worker node for a UniqueID e.g. UniqueID 71354 has two <Misc> nodes with same Date but with different BonusAmount

Current XSLT

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>
    
    <xsl:template match="/Workers">
        
        <Employees>
            <xsl:variable name="undated" select="Worker[not(Misc)]"/>
            <xsl:for-each-group select="Worker/Misc" group-by="Date">
                <EmployeeRecords>
                    <xsl:copy-of select="$undated"/>
                    <xsl:copy-of select="..[not(Misc)]/(* except Misc)"/>
                    <xsl:for-each select="current-group()">
                        <Employee>
                            <xsl:for-each select=".">
                                <xsl:copy-of select="..[Misc]/(* except Misc)"/>
                                <xsl:copy-of select="."/>
                            </xsl:for-each>
                        </Employee>
                    </xsl:for-each>
                </EmployeeRecords>
            </xsl:for-each-group>
        </Employees>
        
    </xsl:template>
</xsl:stylesheet>

Current output

<?xml version="1.0" encoding="UTF-8"?>
<Employees>
    <EmployeeRecords>
        <Worker> <!-- name of this element should be <Employee> instead of <Worker> -->
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Worker>
        <Worker> <!-- name of this element should be <Employee> instead of <Worker> -->
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Worker>
        <Employee>
            <UniqueID>71354</UniqueID>
            <ExpenseAmount>998.00</ExpenseAmount>
            <Misc> <!-- Misc node that follow this node should be copied under the parent <Employee> node because of same Unique ID 71354 and same <Date> -->
                <Date>2023-10-10-07:00</Date>
                <BonusAmount>400.00</BonusAmount>
            </Misc>
        </Employee>
        <Employee>
            <UniqueID>71354</UniqueID>
            <ExpenseAmount>998.00</ExpenseAmount>
            <Misc>  <!-- This Misc node should be copied above because of same Unique ID 71354 and same <Date> -->
                <Date>2023-10-10-07:00</Date>
                <BonusAmount>520.00</BonusAmount>
            </Misc>
        </Employee>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-10-07:00</Date>
                <BonusAmount>600.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
    <EmployeeRecords>
        <Worker>
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Worker>
        <Worker>
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Worker>
        <Employee>
            <UniqueID>71354</UniqueID>
            <ExpenseAmount>998.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-09-07:00</Date>
                <BonusAmount>900.00</BonusAmount>
            </Misc>
        </Employee>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-09-07:00</Date>
                <BonusAmount>410.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
    <EmployeeRecords>
        <Worker>
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Worker>
        <Worker>
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Worker>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-06-07:00</Date>
                <BonusAmount>322.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
    <EmployeeRecords>
        <Worker>
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Worker>
        <Worker>
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Worker>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-08-07:00</Date>
                <BonusAmount>800.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
</Employees>

Expected Output

Issue

  • Name of element that doesn't have Misc node should have been <Employee> instead of Worker. I tried changing element name but the result is incorrect because of variable $undated declaration.
  • Currently two Employee nodes are showing in my current output for UniqueID with value 71354 so Misc node under a Worker node for same UniqueID and Date should be copied under one Employee parent node.

Expected Output

<?xml version="1.0" encoding="UTF-8"?>
<Employees>
    <EmployeeRecords>
        <Employee>
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>71354</UniqueID>
            <ExpenseAmount>998.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-10-07:00</Date>
                <BonusAmount>400.00</BonusAmount>
            </Misc>
            <Misc>
                <Date>2023-10-10-07:00</Date>
                <BonusAmount>520.00</BonusAmount>
            </Misc>
        </Employee>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-10-07:00</Date>
                <BonusAmount>600.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
    <EmployeeRecords>
        <Employee>
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>71354</UniqueID>
            <ExpenseAmount>998.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-09-07:00</Date>
                <BonusAmount>900.00</BonusAmount>
            </Misc>
        </Employee>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-09-07:00</Date>
                <BonusAmount>410.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
    <EmployeeRecords>
        <Employee>
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-06-07:00</Date>
                <BonusAmount>322.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
    <EmployeeRecords>
        <Employee>
            <UniqueID>67896</UniqueID>
            <ExpenseAmount>400.00</ExpenseAmount>
            <BonusAmount>230.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>12465</UniqueID>
            <ExpenseAmount>500.00</ExpenseAmount>
            <BonusAmount>430.00</BonusAmount>
        </Employee>
        <Employee>
            <UniqueID>45679</UniqueID>
            <ExpenseAmount>1507.00</ExpenseAmount>
            <Misc>
                <Date>2023-10-08-07:00</Date>
                <BonusAmount>800.00</BonusAmount>
            </Misc>
        </Employee>
    </EmployeeRecords>
</Employees>

The application that I'm using supports both XSLT 2.0 and XSLT 3.0 so I'm tagging the question to both XSLT 2.0 and 3.0

Any help is appreciated here to fix the issue. Thanks

xslt-2.0 xslt-3.0
Original Q&A
0

There are 0 best solutions below

Related Questions in XSLT-2.0

Related Questions in XSLT-3.0

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.