Search code examples
xmlxsltxslt-2.0

XSLT for-each-group resulted in duplicate nodes

I need to transform source XML that has 2 layer of data (employee and addresses) and multiple records of same employee with different addresses to group-by the employee ID. This is with XSL 2.0. Below is my source XML, XSLT and current output. For the first record with 2 addresses, the output duplicated the 2 addresses twice. I tried to move the <xsl:for-each> that handles the Addresses_group to outer-loop but that resulted in no data. Need help on right approach to do this. Thanks.

<?xml version='1.0' encoding='UTF-8'?>
<wd:Report_Data xmlns:wd="urn:com.abc/bsvc">
    <wd:Report_Entry>
        <wd:EMPLID>12345</wd:EMPLID>
        <wd:N_BLDG_ID>N</wd:N_BLDG_ID>
        <wd:LOCATION_NAME>San Diego</wd:LOCATION_NAME>
        <wd:ACTION_EFF_DT>1020-01-06</wd:ACTION_EFF_DT>
        <wd:Address_Usage>home</wd:Address_Usage>
        <wd:Addresses_group>
            <wd:Address_ID>ADDRESS_REFERENCE-333</wd:Address_ID>
            <wd:ADDRESS1>#112233</wd:ADDRESS1>
            <wd:ADDRESS2>P.O.Box 222</wd:ADDRESS2>
            <wd:CITY>Moore</wd:CITY>
            <wd:STATE>NJ</wd:STATE>
            <wd:ZIP>07945</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
        <wd:Addresses_group>
            <wd:Address_ID>ADDRESS_REFERENCE-6-444</wd:Address_ID>
            <wd:ADDRESS1>123 Good Ave</wd:ADDRESS1>
            <wd:CITY>Carlsbad</wd:CITY>
            <wd:STATE>CA</wd:STATE>
            <wd:ZIP>92011</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:EMPLID>12345</wd:EMPLID>
        <wd:N_BLDG_ID>N</wd:N_BLDG_ID>
        <wd:LOCATION_NAME>San Diego</wd:LOCATION_NAME>
        <wd:ACTION_EFF_DT>2016-06-27</wd:ACTION_EFF_DT>
        <wd:Address_Usage>work</wd:Address_Usage>
        <wd:Addresses_group>
            <wd:Address_ID>ADDRESS_152</wd:Address_ID>
            <wd:ADDRESS1>Remote Office</wd:ADDRESS1>
            <wd:CITY>San Diego</wd:CITY>
            <wd:STATE>CA</wd:STATE>
            <wd:ZIP>92121</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:EMPLID>12345</wd:EMPLID>
        <wd:N_BLDG_ID>N</wd:N_BLDG_ID>
        <wd:LOCATION_NAME>San Diego</wd:LOCATION_NAME>
        <wd:ACTION_EFF_DT>2016-06-27</wd:ACTION_EFF_DT>
        <wd:Address_Usage>IA</wd:Address_Usage>
        <wd:Addresses_group>
            <wd:Address_ID>ADDRESS_555</wd:Address_ID>
            <wd:ADDRESS1>ABC Office1</wd:ADDRESS1>
            <wd:CITY>SleepyTown</wd:CITY>
            <wd:STATE>CA</wd:STATE>
            <wd:ZIP>11223</wd:ZIP>
            <wd:COUNTRY>CA</wd:COUNTRY>
        </wd:Addresses_group>
    </wd:Report_Entry>
</wd:Report_Data>

Below is my XSLT:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions"
    xmlns:wd="urn:com.abc/bsvc" xmlns:this="this-worksheet" exclude-result-prefixes="xsl"
    version="2.0">

    <xsl:variable name="vLinefeed" select="'&#x0D;&#x0A;'"/>
    <xsl:variable name="vDelimiter" select="';'"/>

    <xsl:template match="/">
        <wd:Report_Data>
            <xsl:apply-templates select="wd:Report_Data"/>
        </wd:Report_Data>
    </xsl:template>

    <xsl:template match="wd:Report_Data">
        <xsl:for-each-group select="wd:Report_Entry" group-by="concat(wd:EMPLID,wd:N_BLDG_ID,wd:LOCATION_NAME)">
            <wd:Report_Entry>
                <wd:EMPLID>
                    <xsl:value-of select="wd:EMPLID"/>
                </wd:EMPLID>
                <wd:N_BLDG_ID>
                    <xsl:value-of select="wd:N_BLDG_ID"/>
                </wd:N_BLDG_ID>
                <wd:LOCATION_NAME>
                    <xsl:value-of select="wd:LOCATION_NAME"/>
                </wd:LOCATION_NAME>    
                <xsl:for-each-group select="current-group()" group-by="wd:Addresses_group">
                    <xsl:for-each select="wd:Addresses_group">
                        <wd:Addresses_group>  
                            <wd:EFF_DT>
                                <xsl:value-of select="../wd:ACTION_EFF_DT"/>
                            </wd:EFF_DT>          
                            <wd:ADDRESS_TYPE>
                                <xsl:value-of select="../wd:Address_Usage"/>
                            </wd:ADDRESS_TYPE>
                            <wd:ADDRESS1>
                                <xsl:value-of select="wd:ADDRESS1"/>
                            </wd:ADDRESS1>
                            <wd:ADDRESS2>
                                <xsl:value-of select="wd:ADDRESS2"/>
                            </wd:ADDRESS2>
                            <wd:ADDRESS3>
                                <xsl:value-of select="wd:ADDRESS3"/>
                            </wd:ADDRESS3>                    
                            <wd:CITY>
                                <xsl:value-of select="wd:CITY"/>
                            </wd:CITY>
                            <wd:STATE>
                                <xsl:value-of select="wd:STATE"/>
                            </wd:STATE>
                            <wd:ZIP>
                                <xsl:value-of select="wd:ZIP"/>
                            </wd:ZIP>
                            <wd:COUNTRY>
                                <xsl:value-of select="wd:COUNTRY"/>
                            </wd:COUNTRY>
                        </wd:Addresses_group>
                    </xsl:for-each>                    
                </xsl:for-each-group>                    
            </wd:Report_Entry>
        </xsl:for-each-group>    
    </xsl:template>    
</xsl:stylesheet>

Below is the current output:

<?xml version="1.0" encoding="UTF-8"?>
<wd:Report_Data xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:wd="urn:com.abc/bsvc"
    xmlns:this="this-worksheet">
    <wd:Report_Entry>
        <wd:EMPLID>12345</wd:EMPLID>
        <wd:N_BLDG_ID>N</wd:N_BLDG_ID>
        <wd:LOCATION_NAME>San Diego</wd:LOCATION_NAME>
        <wd:Addresses_group>
            <wd:EFF_DT>1020-01-06</wd:EFF_DT>
            <wd:ADDRESS_TYPE>home</wd:ADDRESS_TYPE>
            <wd:ADDRESS1>#112233</wd:ADDRESS1>
            <wd:ADDRESS2>P.O.Box 222</wd:ADDRESS2>
            <wd:ADDRESS3/>
            <wd:CITY>Moore</wd:CITY>
            <wd:STATE>NJ</wd:STATE>
            <wd:ZIP>07945</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
        <wd:Addresses_group>
            <wd:EFF_DT>1020-01-06</wd:EFF_DT>
            <wd:ADDRESS_TYPE>home</wd:ADDRESS_TYPE>
            <wd:ADDRESS1>123 Good Ave</wd:ADDRESS1>
            <wd:ADDRESS2/>
            <wd:ADDRESS3/>
            <wd:CITY>Carlsbad</wd:CITY>
            <wd:STATE>CA</wd:STATE>
            <wd:ZIP>92011</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
        <wd:Addresses_group>
            <wd:EFF_DT>1020-01-06</wd:EFF_DT>
            <wd:ADDRESS_TYPE>home</wd:ADDRESS_TYPE>
            <wd:ADDRESS1>#112233</wd:ADDRESS1>
            <wd:ADDRESS2>P.O.Box 222</wd:ADDRESS2>
            <wd:ADDRESS3/>
            <wd:CITY>Moore</wd:CITY>
            <wd:STATE>NJ</wd:STATE>
            <wd:ZIP>07945</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
        <wd:Addresses_group>
            <wd:EFF_DT>1020-01-06</wd:EFF_DT>
            <wd:ADDRESS_TYPE>home</wd:ADDRESS_TYPE>
            <wd:ADDRESS1>123 Good Ave</wd:ADDRESS1>
            <wd:ADDRESS2/>
            <wd:ADDRESS3/>
            <wd:CITY>Carlsbad</wd:CITY>
            <wd:STATE>CA</wd:STATE>
            <wd:ZIP>92011</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
        <wd:Addresses_group>
            <wd:EFF_DT>2016-06-27</wd:EFF_DT>
            <wd:ADDRESS_TYPE>work</wd:ADDRESS_TYPE>
            <wd:ADDRESS1>Remote Office</wd:ADDRESS1>
            <wd:ADDRESS2/>
            <wd:ADDRESS3/>
            <wd:CITY>San Diego</wd:CITY>
            <wd:STATE>CA</wd:STATE>
            <wd:ZIP>92121</wd:ZIP>
            <wd:COUNTRY>US</wd:COUNTRY>
        </wd:Addresses_group>
        <wd:Addresses_group>
            <wd:EFF_DT>2016-06-27</wd:EFF_DT>
            <wd:ADDRESS_TYPE>IA</wd:ADDRESS_TYPE>
            <wd:ADDRESS1>ABC Office1</wd:ADDRESS1>
            <wd:ADDRESS2/>
            <wd:ADDRESS3/>
            <wd:CITY>SleepyTown</wd:CITY>
            <wd:STATE>CA</wd:STATE>
            <wd:ZIP>11223</wd:ZIP>
            <wd:COUNTRY>CA</wd:COUNTRY>
        </wd:Addresses_group>
    </wd:Report_Entry>
</wd:Report_Data>
Solution

  • I think instead of

               <xsl:for-each-group select="current-group()" group-by="wd:Addresses_group">
                <xsl:for-each select="wd:Addresses_group">

    you want

               <xsl:for-each-group select="current-group()/wd:Addresses_group" group-by=".">