CODEWITHSUNDEEP
xsltxslt-1.0xslt-2.0xslt-3.0xslt-grouping
Pavan Kumar
Pavan Kumar

Reputation: 5

XSLT issue with the concatenation when empty fields

Requirement is to create a CSV file out of an XML for each Employee Records. Each record in CSV contains multiple lines which depends on the department level which I have achieved it through the attached XSLT. My XSLT is not giving the exact output, looking for a help from the experts here to achieve the requirement.

Input XML:

<EmpJob>
    <EmpJob>
        <locationNav>
            <FOLocation>
                <externalCode>100445</externalCode>
                <name>Guanggang</name>
            </FOLocation>
        </locationNav>
        <countryOfCompany>ASD</countryOfCompany>
        <company>1784</company>
        <location>100445</location>
        <department>01058415</department>
        <departmentNav>
            <FODepartment>
                <parent>01033725</parent>
                <FODepartment>
                    <name_defaultValue>South_S4</name_defaultValue>
                    <externalCode>01033725</externalCode>
                    <parentNav>
                        <FODepartment>
                            <name_defaultValue>Sales_South</name_defaultValue>
                            <externalCode>01018548</externalCode>
                            <parentNav>
                                <FODepartment>
                                    <name_defaultValue>BU</name_defaultValue>
                                    <externalCode>01022533</externalCode>
                                    <parentNav>
                                        <FODepartment>
                                            <name_defaultValue>China</name_defaultValue>
                                            <externalCode>00222742</externalCode>
                                            <parentNav>
                                                <FODepartment>
                                                    <name_defaultValue>China_International</name_defaultValue>
                                                    <externalCode>01085010</externalCode>
                                                    <parentNav>
                                                        <FODepartment>
                                                            <name_defaultValue>Healthcare</name_defaultValue>
                                                            <externalCode>00181661</externalCode>
                                                            <parentNav>
                                                                <FODepartment>
                                                                    <name_defaultValue>Global</name_defaultValue>
                                                                    <externalCode>00181660</externalCode>
                                                                    <parentNav/>
                                                                </FODepartment>
                                                            </parentNav>
                                                        </FODepartment>
                                                    </parentNav>
                                                </FODepartment>
                                            </parentNav>
                                        </FODepartment>
                                    </parentNav>
                                </FODepartment>
                            </parentNav>
                        </FODepartment>
                    </parentNav>
                </FODepartment>
                <externalCode>01058415</externalCode>
                <name>North</name>
            </FODepartment>
        </departmentNav>
        <userId>M111111</userId>
    </EmpJob>
    <EmpJob>
        <locationNav>
            <FOLocation>
                <externalCode>801029</externalCode>
                <name>Darmstadt</name>
            </FOLocation>
        </locationNav>
        <countryOfCompany>DIU</countryOfCompany>
        <company>1500</company>
        <location>801029</location>
        <department>00047332</department>
        <departmentNav>
            <FODepartment>
                <parent>01039877</parent>
                <FODepartment>
                    <name_defaultValue>Group_Leads</name_defaultValue>
                    <externalCode>01039877</externalCode>
                    <parentNav>
                        <FODepartment>
                            <name_defaultValue>Group_Managers</name_defaultValue>
                            <externalCode>01009148</externalCode>
                            <parentNav>
                                <FODepartment>
                                    <name_defaultValue>Group_Owners</name_defaultValue>
                                    <externalCode>01005929</externalCode>
                                    <parentNav>
                                        <FODepartment>
                                            <name_defaultValue>Group_Functions</name_defaultValue>
                                            <externalCode>00000002</externalCode>
                                            <parentNav>
                                                <FODepartment>
                                                    <name_defaultValue>Local_Functions</name_defaultValue>
                                                    <externalCode>01005825</externalCode>
                                                    <parentNav>
                                                        <FODepartment>
                                                            <name_defaultValue>Global</name_defaultValue>
                                                            <externalCode>00181660</externalCode>
                                                            <parentNav/>
                                                        </FODepartment>
                                                    </parentNav>
                                                </FODepartment>
                                            </parentNav>
                                        </FODepartment>
                                    </parentNav>
                                </FODepartment>
                            </parentNav>
                        </FODepartment>
                    </parentNav>
                </FODepartment>
                <externalCode>00047332</externalCode>
                <name>Interim_Services</name>
            </FODepartment>
        </departmentNav>
        <userId>M222222</userId>
    </EmpJob>
    <EmpJob>
        <locationNav>
            <FOLocation>
                <externalCode>100464</externalCode>
                <name>Milan</name>
            </FOLocation>
        </locationNav>
        <countryOfCompany>ITA</countryOfCompany>
        <company>2000</company>
        <location>100464</location>
        <department>01071591</department>
        <departmentNav>
            <FODepartment>
                <parent>01071590</parent>
                <FODepartment>
                    <name_defaultValue>Execution</name_defaultValue>
                    <externalCode>01071590</externalCode>
                    <parentNav>
                        <FODepartment>
                            <name_defaultValue>Commercial</name_defaultValue>
                            <externalCode>1203477</externalCode>
                            <parentNav>
                                <FODepartment>
                                    <name_defaultValue>Lab_Solutions</name_defaultValue>
                                    <externalCode>1203469</externalCode>
                                    <parentNav>
                                        <FODepartment>
                                            <name_defaultValue>Science</name_defaultValue>
                                            <externalCode>01034020</externalCode>
                                            <parentNav>
                                                <FODepartment>
                                                    <name_defaultValue>Global</name_defaultValue>
                                                    <externalCode>00181660</externalCode>
                                                    <parentNav/>
                                                </FODepartment>
                                            </parentNav>
                                        </FODepartment>
                                    </parentNav>
                                </FODepartment>
                            </parentNav>
                        </FODepartment>
                    </parentNav>
                </FODepartment>
                <externalCode>01071591</externalCode>
                <name>Direction</name>
            </FODepartment>
        </departmentNav>
        <userId>M306062</userId>
    </EmpJob>
</EmpJob>

Output Expected:

Location Type,Parent Path,Location Name,Effective Date,Expiration Date,Full Name
Department_L7,Merck/ASD/1784/Guanggang/Healthcare/China_International/China/BU/Sales_South/South_S4/North,North,1/1/1900,1/1/3000,North
Department_L6,Merck/ASD/1784/Guanggang/Healthcare/China_International/China/BU/Sales_South/South_S4,South_S4,1/1/1900,1/1/3000,South_S4
Department_L5,Merck/ASD/1784/Guanggang/Healthcare/China_International/China/BU/Sales_South,Sales_South,1/1/1900,1/1/3000,Sales_South
Department_L4,Merck/ASD/1784/Guanggang/Healthcare/China_International/China/BU,BU,1/1/1900,1/1/3000,BU
Department_L3,Merck/ASD/1784/Guanggang/Healthcare/China_International/China,China,1/1/1900,1/1/3000,China
Department_L2,Merck/ASD/1784/Guanggang/Healthcare/China_International,China_International,1/1/1900,1/1/3000,China_International
Department_L1,Merck/ASD/1784/Guanggang/Healthcare,Healthcare,1/1/1900,1/1/3000,Healthcare
LocationCity,Merck/ASD/1784/Guanggang,Guanggang ,1/1/1900,1/1/3000,Guanggang 
Subsidiary,Merck/ASD/1784,1784,1/1/1900,1/1/3000,1784
Country,Merck,ASD,1/1/1900,1/1/3000,ASD
Root,/,Merck,1/1/1900,1/1/3000,Merck

Location Type,Parent Path,Location Name,Effective Date,Expiration Date,Full Name
Department_L6,Merck/DIU/1500/Darmstadt/Local_Functions/Group_Functions/Group_Owners/Group_Managers/Group_Leads/Interim_Services,Interim_Services,1/1/1900,1/1/3000,Interim_Services
Department_L5,Merck/DIU/1500/Darmstadt/Local_Functions/Group_Functions/Group_Owners/Group_Managers/Group_Leads,Group_Leads,1/1/1900,1/1/3000,Group_Leads
Department_L4,Merck/DIU/1500/Darmstadt/Local_Functions/Group_Functions/Group_Owners/Group_Managers,Group_Managers,1/1/1900,1/1/3000,Group_Managers
Department_L3,Merck/DIU/1500/Darmstadt/Local_Functions/Group_Functions/Group_Owners,Group_Owners,1/1/1900,1/1/3000,Group_Owners
Department_L2,Merck/DIU/1500/Darmstadt/Local_Functions/Group_Functions,Group_Functions,1/1/1900,1/1/3000,Group_Functions
Department_L1,Merck/DIU/1500/Darmstadt/Local_Functions,Local_Functions,1/1/1900,1/1/3000,Local_Functions
LocationCity,Merck/DIU/1500/Darmstadt ,Darmstadt ,1/1/1900,1/1/3000,Darmstadt 
Subsidiary,Merck/DIU/1500,1500,1/1/1900,1/1/3000,1500
Country,Merck,DIU,1/1/1900,1/1/3000,DIU
Root,/,Merck,1/1/1900,1/1/3000,Merck

Location Type,Parent Path,Location Name,Effective Date,Expiration Date,Full Name
Department_L5,Merck/ITA/2000/Milan/Science/Lab_Solutions/Commercial/Execution/Direction,Direction,1/1/1900,1/1/3000,Direction
Department_L4,Merck/ITA/2000/Milan/Science/Lab_Solutions/Commercial/Execution,Execution,1/1/1900,1/1/3000,Execution
Department_L3,Merck/ITA/2000/Milan/Science/Lab_Solutions/Commercial,Commercial,1/1/1900,1/1/3000,Commercial
Department_L2,Merck/ITA/2000/Milan/Science/Lab_Solutions,Lab_Solutions,1/1/1900,1/1/3000,Lab_Solutions
Department_L1,Merck/ITA/2000/Milan/Science,Science,1/1/1900,1/1/3000,Science
LocationCity,Merck/ITA/2000/Milan,Milan,1/1/1900,1/1/3000,Milan
Subsidiary,Merck/ITA/2000,2000,1/1/1900,1/1/3000,2000
Country,Merck,ITA,1/1/1900,1/1/3000,ITA
Root,/,Merck,1/1/1900,1/1/3000,Merck

My Current XSLT:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output omit-xml-declaration="yes"/>
    <xsl:mode on-no-match="shallow-copy"/>
    <xsl:param name="Effective_Date"/>
    <xsl:param name="Expiration_Date"/>
    <xsl:param name="Root_Name"/>
    <xsl:output method="text"/>
    <xsl:template match="/">
        <xsl:for-each select="/EmpJob/EmpJob">
            <xsl:text>Location Type,Parent Path,Location Name,Effective Date,Expiration Date,Full Name&#10;</xsl:text>

            <xsl:variable name="Country" select="countryOfCompany"/>
            <xsl:variable name="Subsidiary" select="company"/>
            <xsl:variable name="LocationCity" select="substring(locationNav/FOLocation/name,1,10)"/>
            <xsl:variable name="Subsidiary_L" select="substring(concat($Root_Name, '/', $Country, '/', $Subsidiary), 1, 1000)"/>
            <xsl:variable name="LocationCity_L" select="substring(concat($Subsidiary_L, '/', $LocationCity ), 1, 1000)"/>
            
            <xsl:variable name="FODepartment_L1" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L2" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L3" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L4" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L5" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L6" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/parentNav/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L7" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L8" select="substring(departmentNav/FODepartment/FODepartment/parentNav/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L9" select="substring(departmentNav/FODepartment/FODepartment/name_defaultValue, 1, 50)"/>
            <xsl:variable name="FODepartment_L10" select="substring(departmentNav/FODepartment/name, 1, 50)"/>
            
            <xsl:variable name="Department_L10" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3, '/',$FODepartment_L4, '/',$FODepartment_L5, '/',$FODepartment_L6, '/',$FODepartment_L7, '/',$FODepartment_L8, '/',$FODepartment_L9, '/',$FODepartment_L10), 1, 1000)"/>
            <xsl:variable name="Department_L9" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3, '/',$FODepartment_L4, '/',$FODepartment_L5, '/',$FODepartment_L6, '/',$FODepartment_L7, '/',$FODepartment_L8, '/',$FODepartment_L9), 1, 1000)"/>
            <xsl:variable name="Department_L8" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3, '/',$FODepartment_L4, '/',$FODepartment_L5, '/',$FODepartment_L6, '/',$FODepartment_L7, '/',$FODepartment_L8), 1, 1000)"/>
            <xsl:variable name="Department_L7" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3, '/',$FODepartment_L4, '/',$FODepartment_L5, '/',$FODepartment_L6, '/',$FODepartment_L7), 1, 1000)"/>
            <xsl:variable name="Department_L6" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3, '/',$FODepartment_L4, '/',$FODepartment_L5, '/',$FODepartment_L6), 1, 1000)"/>
            <xsl:variable name="Department_L5" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3, '/',$FODepartment_L4, '/',$FODepartment_L5), 1, 1000)"/>  
            <xsl:variable name="Department_L4" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3, '/',$FODepartment_L4), 1, 1000)"/>    
            <xsl:variable name="Department_L3" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2, '/',$FODepartment_L3), 1, 1000)"/>         
            <xsl:variable name="Department_L2" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1, '/',$FODepartment_L2), 1, 1000)"/>
            <xsl:variable name="Department_L1" select="substring(concat($Root_Name, '/',$Country, '/', $Subsidiary, '/', $LocationCity, '/',$FODepartment_L1), 1, 1000)"/>
            
            <xsl:if test="not($FODepartment_L1 = '')">
                <xsl:value-of select="concat('Department_L10', ',', $Department_L10, ',', $FODepartment_L10, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L10)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L2 = '')">
                <xsl:value-of select="concat('Department_L9', ',', $Department_L9, ',', $FODepartment_L9, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L9)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L3 = '')">
                <xsl:value-of select="concat('Department_L8', ',', $Department_L8, ',', $FODepartment_L8, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L8)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L4 = '')">
                <xsl:value-of select="concat('Department_L7', ',', $Department_L7, ',', $FODepartment_L7, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L7)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L5 = '')">
                <xsl:value-of select="concat('Department_L6', ',', $Department_L6, ',', $FODepartment_L6, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L6)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L6 = '')">
                <xsl:value-of select="concat('Department_L5', ',', $Department_L5, ',', $FODepartment_L5, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L5)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L7 = '')">
                <xsl:value-of select="concat('Department_L4', ',', $Department_L4, ',', $FODepartment_L4, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L4)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L8 = '')">
                <xsl:value-of select="concat('Department_L3', ',', $Department_L3, ',', $FODepartment_L3, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L3)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L9 = '')">
                <xsl:value-of select="concat('Department_L2', ',', $Department_L2, ',', $FODepartment_L2, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L2)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:if test="not($FODepartment_L10 = '')">
                <xsl:value-of select="concat('Department_L1', ',', $Department_L1, ',', $FODepartment_L1, ',', $Effective_Date, ',', $Expiration_Date, ',', $FODepartment_L1)"/><xsl:text>&#10;</xsl:text>
            </xsl:if>
            <xsl:value-of select="concat('LocationCity', ',', $LocationCity_L, ',', $LocationCity, ',', $Effective_Date, ',', $Expiration_Date, ',', $LocationCity)"/><xsl:text>&#10;</xsl:text>
            <xsl:value-of select="concat('Subsidiary', ',', $Subsidiary_L, ',', $Subsidiary, ',', $Effective_Date, ',', $Expiration_Date, ',', $Subsidiary)"/><xsl:text>&#10;</xsl:text>
            <xsl:value-of select="concat('Country', ',', $Root_Name, ',', $Country, ',', $Effective_Date, ',', $Expiration_Date, ',', $Country)"/><xsl:text>&#10;</xsl:text>
            <xsl:value-of select="concat('Root', ',', '/', ',', $Root_Name, ',', $Effective_Date, ',', $Expiration_Date, ',', $Root_Name)"/><xsl:text>&#10;</xsl:text>

            <xsl:text>&#10;</xsl:text>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Upvotes: 0

Views: 52

Answers (0)

Related Questions