XSLT分组映射转换问题求助：员工工作记录分组需求

输入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>

期望输出XML #

<?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>

转换规则 #

• 按员工userId分组，每个员工对应一个<employee>节点
• 当员工记录的EventReason为9000或9002时，触发新<job>节点的创建：
  • 该节点的JobDescription和EndDate取自触发节点的上一条记录，StartDate取当前工作阶段的最早值
• 新<job>节点的填充规则：
  • StartDate取自当前EventReason为9000/9002的记录
  • 若该员工还有其他9000/9002记录，则JobDescription和EndDate取下一个9000/9002记录的上一条记录值
  • 若无其他此类记录，则取员工最后一条记录的对应值
• 员工首个工作阶段（第一个9000/9002记录之前）合并为一个<job>，取阶段内最早StartDate和最晚EndDate

XSLT解决方案 #

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

    <!-- 按userId分组员工记录 -->
    <xsl:key name="employee-by-id" match="row" use="userId"/>

    <!-- 主模板 -->
    <xsl:template match="/root">
        <root>
            <!-- 遍历每个唯一员工 -->
            <xsl:for-each select="row[generate-id() = generate-id(key('employee-by-id', userId)[1])]">
                <employee>
                    <xsl:copy-of select="userId"/>
                    <!-- 获取当前员工的所有记录 -->
                    <xsl:variable name="employee-rows" select="key('employee-by-id', userId)"/>
                    <!-- 获取当前员工的所有分割点（EventReason为9000或9002的记录） -->
                    <xsl:variable name="split-points" select="$employee-rows[EventReason='9000' or EventReason='9002']"/>

                    <!-- 处理第一个分割点之前的记录 -->
                    <xsl:if test="count($split-points) > 0">
                        <xsl:variable name="first-split-pos">
                            <xsl:call-template name="get-position">
                                <xsl:with-param name="target" select="$split-points[1]"/>
                                <xsl:with-param name="nodeset" select="$employee-rows"/>
                            </xsl:call-template>
                        </xsl:variable>
                        <xsl:if test="$first-split-pos > 1">
                            <job>
                                <JobDescription><xsl:value-of select="$employee-rows[1]/JobDescription"/></JobDescription>
                                <StartDate><xsl:value-of select="$employee-rows[1]/StartDate"/></StartDate>
                                <EndDate><xsl:value-of select="$employee-rows[$first-split-pos - 1]/EndDate"/></EndDate>
                            </job>
                        </xsl:if>
                    </xsl:if>

                    <!-- 处理每个分割点对应的job -->
                    <xsl:for-each select="$split-points">
                        <xsl:variable name="current-index" select="position()"/>
                        <xsl:variable name="current-split-pos">
                            <xsl:call-template name="get-position">
                                <xsl:with-param name="target" select="."/>
                                <xsl:with-param name="nodeset" select="$employee-rows"/>
                            </xsl:call-template>
                        </xsl:variable>
                        <!-- 获取下一个分割点 -->
                        <xsl:variable name="next-split" select="$split-points[$current-index + 1]"/>
                        <!-- 确定当前job的结束记录位置 -->
                        <xsl:variable name="end-pos">
                            <xsl:choose>
                                <xsl:when test="$next-split">
                                    <xsl:call-template name="get-position">
                                        <xsl:with-param name="target" select="$next-split"/>
                                        <xsl:with-param name="nodeset" select="$employee-rows"/>
                                    </xsl:call-template>
                                </xsl:when>
                                <xsl:otherwise><xsl:value-of select="count($employee-rows)"/></xsl:otherwise>
                            </xsl:choose>
                        </xsl:variable>

                        <job>
                            <JobDescription><xsl:value-of select="$employee-rows[$end-pos - ($next-split and 1)]/JobDescription"/></JobDescription>
                            <StartDate><xsl:value-of select="StartDate"/></StartDate>
                            <EndDate><xsl:value-of select="$employee-rows[$end-pos - ($next-split and 1)]/EndDate"/></EndDate>
                        </job>
                    </xsl:for-each>

                    <!-- 无分割点时合并所有记录为一个job -->
                    <xsl:if test="count($split-points) = 0">
                        <job>
                            <JobDescription><xsl:value-of select="$employee-rows[1]/JobDescription"/></JobDescription>
                            <StartDate><xsl:value-of select="$employee-rows[1]/StartDate"/></StartDate>
                            <EndDate><xsl:value-of select="$employee-rows[last()]/EndDate"/></EndDate>
                        </job>
                    </xsl:if>
                </employee>
            </xsl:for-each>
        </root>
    </xsl:template>

    <!-- 辅助模板：获取节点在节点集中的位置 -->
    <xsl:template name="get-position">
        <xsl:param name="target"/>
        <xsl:param name="nodeset"/>
        <xsl:for-each select="$nodeset">
            <xsl:if test=". is $target">
                <xsl:value-of select="position()"/>
            </xsl:if>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

方案说明 #

1. 通过<xsl:key>实现员工记录的userId分组
2. 识别每个员工的分割点（EventReason为9000/9002的记录），以此划分工作阶段
3. 分别处理分割点前的记录、分割点对应的阶段记录，以及无分割点的合并场景
4. 辅助模板get-position用于获取节点在员工记录集中的位置，支撑阶段范围的计算

内容的提问来源于stack exchange，提问作者Carlos Moraes