Search code examples
xsltforeachxslt-grouping

First row for the Report_Entry(worker) needs to be defaulted to "YES". All other rows for worker to return "NO"

I'm working on a data extraction program to pull phone numbers with types including Work mobile, Work Telephone, Home Mobile and Home Telephone. If there are duplicate usage types, I'm picking the first instance. I'm needing help setting a flag for "YES" on first row for each worker. Lets us the program generates 3 rows for me, the first row needs to be set to YES and all other rows should return NO. (LAST COLUMN) Any help is greatly appreciated. Thanks

XML:

<wd:Report_Data xmlns:wd="urn:com.workday.report/Test">
<wd:Report_Entry>
    <wd:UNIQUE_IDENTIFIER>123</wd:UNIQUE_IDENTIFIER>
    <wd:PHONE_NUMBERS>
        <wd:PHONE_TYPE>Work-Telephone</wd:PHONE_TYPE>
        <wd:PHONE_NUMBER>123-3456</wd:PHONE_NUMBER>
        <wd:COUNTRY_CODE_NUMBER>1</wd:COUNTRY_CODE_NUMBER>
        <wd:AREA_CODE>900</wd:AREA_CODE>
        <wd:PRIMARY_FLAG>YES</wd:PRIMARY_FLAG>
    </wd:PHONE_NUMBERS>
    <wd:PHONE_NUMBERS>
        <wd:PHONE_TYPE>Home-Mobile</wd:PHONE_TYPE>
        <wd:PHONE_NUMBER>854-5672</wd:PHONE_NUMBER>
        <wd:COUNTRY_CODE_NUMBER>1</wd:COUNTRY_CODE_NUMBER>
        <wd:AREA_CODE>954</wd:AREA_CODE>
        <wd:PRIMARY_FLAG>YES</wd:PRIMARY_FLAG>
    </wd:PHONE_NUMBERS>
    <wd:PHONE_NUMBERS>
        <wd:PHONE_TYPE>Home-Telephone</wd:PHONE_TYPE>
        <wd:PHONE_NUMBER>745-5431</wd:PHONE_NUMBER>
        <wd:COUNTRY_CODE_NUMBER>1</wd:COUNTRY_CODE_NUMBER>
        <wd:AREA_CODE>999</wd:AREA_CODE>
        <wd:PRIMARY_FLAG>YES</wd:PRIMARY_FLAG>
    </wd:PHONE_NUMBERS>
</wd:Report_Entry>

    <wd:Report_Entry>
    <wd:UNIQUE_IDENTIFIER>789</wd:UNIQUE_IDENTIFIER>
    <wd:PHONE_NUMBERS>
        <wd:PHONE_TYPE>Work-Telephone</wd:PHONE_TYPE>
        <wd:PHONE_NUMBER>713-9856</wd:PHONE_NUMBER>
        <wd:COUNTRY_CODE_NUMBER>1</wd:COUNTRY_CODE_NUMBER>
        <wd:AREA_CODE>900</wd:AREA_CODE>
        <wd:PRIMARY_FLAG>YES</wd:PRIMARY_FLAG>
    </wd:PHONE_NUMBERS>
    <wd:PHONE_NUMBERS>
        <wd:PHONE_TYPE>Home-Mobile</wd:PHONE_TYPE>
        <wd:PHONE_NUMBER>888-9999</wd:PHONE_NUMBER>
        <wd:COUNTRY_CODE_NUMBER>1</wd:COUNTRY_CODE_NUMBER>
        <wd:AREA_CODE>544</wd:AREA_CODE>
        <wd:PRIMARY_FLAG>YES</wd:PRIMARY_FLAG>
    </wd:PHONE_NUMBERS>
    <wd:PHONE_NUMBERS>
        <wd:PHONE_TYPE>Home-Mobile</wd:PHONE_TYPE>
        <wd:PHONE_NUMBER>555-5345</wd:PHONE_NUMBER>
        <wd:COUNTRY_CODE_NUMBER>1</wd:COUNTRY_CODE_NUMBER>
        <wd:AREA_CODE>544</wd:AREA_CODE>
        <wd:PRIMARY_FLAG>YES</wd:PRIMARY_FLAG>
    </wd:PHONE_NUMBERS>
</wd:Report_Entry>

</wd:Report_Data>

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"
    exclude-result-prefixes="xs"
    xmlns:wd="urn:com.workday.report/ERP-HCM-CR_PERSON_PHONE"
    version="2.0">
    <xsl:output method="text"/>
    <xsl:variable name="linefeed" select="'&#xD;&#xA;'"/>
    <xsl:variable name="pipe" select="'|'"/>
    <xsl:variable name="EffectiveStartDate" select="'01-01-1951'"/>
    <xsl:variable name="EffectiveEndDate" select="'4712-12-31'"/>

    <xsl:param name="quote">"</xsl:param>
    <xsl:template match="/">
        <!--  File Header Record  -->
        <xsl:call-template name="Write-Header-Record0"/>
        <xsl:call-template name="Write-Header-Record1"/>
        <xsl:call-template name="Write-Header-Record2"/>
        <!--  File Detail Layout  -->
        <xsl:for-each select="wd:Report_Data/wd:Report_Entry[not(wd:UNIQUE_IDENTIFIER=preceding-sibling::node()/wd:UNIQUE_IDENTIFIER)]"> 
            <xsl:for-each-group select="wd:PHONE_NUMBERS" group-by="(wd:PHONE_TYPE)">
                <xsl:call-template name="Write-Detail-Record"/>
            </xsl:for-each-group> 
        </xsl:for-each>

    </xsl:template>

    <!-- File Header Record 0-->
    <xsl:template name ="Write-Header-Record0">

        <xsl:text>UniqueIdentifier</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>Country</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>PhoneType</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>DateFrom</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>DateTo</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>PhoneNumber</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>CountryCodeNumber</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>Extension</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>SpeedDialNumber</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>AreaCode</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>PrimaryFlag</xsl:text>

        <xsl:value-of select="$linefeed"/>
    </xsl:template>

    <!-- File Header Record 1-->
    <xsl:template name ="Write-Header-Record1">

        <xsl:text>UNIQUE_IDENTIFIER</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>COUNTRY_CODE</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>PHONE_TYPE</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>DATE_FROM</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>DATE_TO</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>PHONE_NUMBER</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>COUNTRY_CODE_NUMBER</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>EXTENSION</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>SPEED_DIAL_NUMBER</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>AREA_CODE</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>PRIMARY_FLAG</xsl:text>

        <xsl:value-of select="$linefeed"/>
    </xsl:template>

    <!-- File Header Record 2-->
    <xsl:template name ="Write-Header-Record2">

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>DATE</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>DATE</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>
        <xsl:value-of select="$pipe"/>

        <xsl:text>VARCHAR2</xsl:text>

        <xsl:value-of select="$linefeed"/>
    </xsl:template>

    <xsl:template name="Write-Detail-Record">

        <xsl:value-of select="../wd:UNIQUE_IDENTIFIER"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="../wd:COUNTRY_CODE"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:PHONE_TYPE"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="../format-date(wd:DATE_FROM,'[Y0001]-[M01]-[D01]')"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="../format-date(wd:DATE_TO,'[Y0001]-[M01]-[D01]')"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="translate(replace(wd:PHONE_NUMBER, '\.',''),'-,','')"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:COUNTRY_CODE_NUMBER"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:EXTENSION"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:SPEED_DIAL_NUMBER"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:AREA_CODE"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:PRIMARY_FLAG"/>
        <xsl:value-of select="$linefeed"/>
    </xsl:template>


</xsl:stylesheet>

Actual Ouput:

UniqueIdentifier|Country|PhoneType|DateFrom|DateTo|PhoneNumber|CountryCodeNumber|Extension|SpeedDialNumber|AreaCode|PrimaryFlag
UNIQUE_IDENTIFIER|COUNTRY_CODE|PHONE_TYPE|DATE_FROM|DATE_TO|PHONE_NUMBER|COUNTRY_CODE_NUMBER|EXTENSION|SPEED_DIAL_NUMBER|AREA_CODE|PRIMARY_FLAG
VARCHAR2|VARCHAR2|VARCHAR2|DATE|DATE|VARCHAR2|VARCHAR2|VARCHAR2|VARCHAR2|VARCHAR2|VARCHAR2
123||Work-Telephone|||1233456|1|||900|YES
123||Home-Mobile|||8545672|1|||954|YES
123||Home-Telephone|||7455431|1|||999|YES
789||Work-Telephone|||7139856|1|||900|YES
789||Home-Mobile|||8889999|1|||544|YES

Actual Output:

UniqueIdentifier|Country|PhoneType|DateFrom|DateTo|PhoneNumber|CountryCodeNumber|Extension|SpeedDialNumber|AreaCode|PrimaryFlag
UNIQUE_IDENTIFIER|COUNTRY_CODE|PHONE_TYPE|DATE_FROM|DATE_TO|PHONE_NUMBER|COUNTRY_CODE_NUMBER|EXTENSION|SPEED_DIAL_NUMBER|AREA_CODE|PRIMARY_FLAG
VARCHAR2|VARCHAR2|VARCHAR2|DATE|DATE|VARCHAR2|VARCHAR2|VARCHAR2|VARCHAR2|VARCHAR2|VARCHAR2
123||Work-Telephone|||1233456|1|||900|YES
123||Home-Mobile|||8545672|1|||954|NO
123||Home-Telephone|||7455431|1|||999|NO
789||Work-Telephone|||7139856|1|||900|YES
789||Home-Mobile|||8889999|1|||544|NO
Solution

  • Consider the following simplified example:

    XSLT 2.0

    <xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xpath-default-namespace="urn:com.workday.report/Test">
<xsl:output method="text"/>

<xsl:template match="/Report_Data">
    <!-- HEADER -->
    <xsl:text>UniqueIdentifier|PhoneType|PhoneNumber|PrimaryFlag&#xD;&#xA;</xsl:text>
    <!-- DATA -->
    <xsl:for-each select="Report_Entry">
        <xsl:variable name="id" select="UNIQUE_IDENTIFIER" />
        <xsl:for-each-group select="PHONE_NUMBERS" group-by="PHONE_TYPE">
            <xsl:value-of select="$id"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="PHONE_TYPE"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="PHONE_NUMBER"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="if (position()=1) then 'YES' else 'NO'"/>
            <xsl:text>&#xD;&#xA;</xsl:text>
        </xsl:for-each-group>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

    Applied to your XML example, this will produce:

    Result

    UniqueIdentifier|PhoneType|PhoneNumber|PrimaryFlag
123|Work-Telephone|123-3456|YES
123|Home-Mobile|854-5672|NO
123|Home-Telephone|745-5431|NO
789|Work-Telephone|713-9856|YES
789|Home-Mobile|888-9999|NO

    Demo: https://xsltfiddle.liberty-development.net/bFWR5E2