XSLT: Issue with mapping transformation (grouping)

Question

any help here it's appreciated.

I need to convert the above input xml:

<?xml version='1.0' encoding='UTF-8'?>
<root>
    <row>
        <userId>40668825871</userId>
        <JobDescription>ANL CONTR QUALIDADE SR</JobDescription>
        <StartDate>01/01/2023</StartDate>
        <EndDate>01/08/2023</EndDate>
        <EventReason>1000</EventReason>
    </row>
    <row>
        <userId>40668825871</userId>
        <JobDescription>ANL CONTR QUALIDADE SR</JobDescription>
        <Atuacao>Interno</Atuacao>
        <StartDate>01/09/2023</StartDate>
        <EndDate>01/31/2023</EndDate>
        <EventReason>1001</EventReason>     
    </row>
    <row>
        <userId>40668825871</userId>
        <JobDescription>ANL CONTR QUALIDADE SR</JobDescription>
        <StartDate>02/01/2023</StartDate>
        <EndDate>02/28/2023</EndDate>
        <EventReason>1003</EventReason>     
    </row>
    <row>
        <userId>40668825871</userId>
        <JobDescription>ANL VALIDACAO SR</JobDescription>
        <StartDate>03/01/2023</StartDate>
        <EndDate>05/05/2023</EndDate>
        <EventReason>9000</EventReason>     
    </row>
    <row>
        <userId>40668825871</userId>
        <JobDescription>ANL VALIDACAO SR</JobDescription>
        <StartDate>05/06/2023</StartDate>
        <EndDate>12/31/2023</EndDate>
        <EventReason>1010</EventReason>     
    </row>
    <row>
        <userId>34916921801</userId>
        <JobDescription>COORD DESENV FARMACOTECNICO</JobDescription>
        <StartDate>08/01/2022</StartDate>
        <EndDate>01/31/2023</EndDate>
        <EventReason>9000</EventReason>     
    </row>
    <row>
        <userId>34916921801</userId>
        <JobDescription>COORD DESENV EMBALAGENS</JobDescription>
        <StartDate>02/01/2023</StartDate>
        <EndDate>02/28/2023</EndDate>
        <EventReason>9002</EventReason>     
    </row>
    <row>
        <userId>34916921801</userId>
        <JobDescription>COORD DESENV EMBALAGENS</JobDescription>
        <StartDate>03/01/2023</StartDate>
        <EndDate>05/05/2023</EndDate>
        <EventReason>1000</EventReason>     
    </row>
    <row>
        <userId>34916921801</userId>
        <JobDescription>COORD DESENV EMBALAGENS</JobDescription>
        <StartDate>05/06/2023</StartDate>
        <EndDate>12/31/2023</EndDate>
        <EventReason>1010</EventReason>     
    </row>
</root>

Into this:

<?xml version='1.0' encoding='UTF-8'?>
<root>
    <employee>
    <userId>40668825871</userId>
        <job>
            <JobDescription>ANL CONTR QUALIDADE SR</JobDescription>
            <StartDate>01/01/2023</StartDate>
            <EndDate>02/28/2023</EndDate>
        </job>
        <job>
            <JobDescription>ANL VALIDACAO SR</JobDescription>
            <StartDate>03/01/2023</StartDate>
            <EndDate>12/31/2023</EndDate>
        </job>
    </employee> 
    <employee>
    <userId>34916921801</userId>
        <job>
            <JobDescription>COORD DESENV FARMACOTECNICO</JobDescription>
            <StartDate>08/01/2022</StartDate>
            <EndDate>01/31/2023</EndDate>
        </job>
        <job>
            <JobDescription>COORD DESENV EMBALAGENS</JobDescription>
            <StartDate>02/01/2023</StartDate>
            <EndDate>02/28/2023</EndDate>
        </job>
        <job>
            <JobDescription>COORD DESENV EMBALAGENS</JobDescription>
            <StartDate>03/01/2023</StartDate>
            <EndDate>12/31/2023</EndDate>
        </job>
    </employee>     
</root>

The input XML it's basically the employees job records of a specific year (so an employee can have one or more records).

The output xml will be an grouping of the records, with the following rules:

1 - Everytime the employee has a eventReason tag with the values 9000 or 9002, it's necessary to create a new job tag with the JobDescription and endDate of the previous record. The startDate will be the oldest startDate of the current employee job.

2 - So, after produce a new job tag, the startDate of the new job will be the startDate of the current record with the event 9000 or 9002 and the JobDescription and endDate will be filled with two possibilities:

2.1 If another event 9000 or 9002 exists, they will be filled with JobDescription and endDate values of the record prior to record with event 9000 or 9002;

2.2 if no other event 9000 or 9002 exists, they will be filled with the values of the employee's last record.

Currently I have no idea how to perform this with XSLT.

Thanks in advance.

The scenario were described above.

Answer 1

I am not sure I read your explanation correctly. It seems you want to group the records by employee, and then create a new job within each group, starting with any row with EventReason 9000 or 9002.

Assuming you can use at least XSLT 2.0, this could be done as:

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

<xsl:template match="/root">
    <output>
        <!-- group by employee -->
        <xsl:for-each-group select="row" group-by="userId">
            <employee>
                <xsl:copy-of select="userId"/>
                    <!-- break the group at each row with EventReason 9000 or 9002"> -->
                    <xsl:for-each-group select="current-group()" group-starting-with="row[EventReason=('9000', '9002')]">
                        <xsl:variable name="last-row" select="current-group()[last()]" />
                        <job>
                            <xsl:copy-of select="$last-row/JobDescription"/>
                            <xsl:copy-of select="StartDate"/>
                            <xsl:copy-of select="$last-row/EndDate"/>
                        </job>
                </xsl:for-each-group>
            </employee>
        </xsl:for-each-group>
    </output>
</xsl:template>

</xsl:stylesheet>

However, the result I am getting with this is different from the expected result you show. So it seems that something is missing in your explanation or in my understanding of it. But at least you should have a starting point.