XML to CSV - some nodes Repeat and sometimes Not - Java/Groovy/XSLT

Question

I wanted to convert xml file to csv, some subnodes may or may not repeat under the parent node, if a subnode repeated then it needs to be a separate line in the output CSV file.

<Person>
    <Person>
      <personalInfoNav>
        <PerPersonal>
          <salutationNav>
            <PicklistOption>
              <externalCode>MR</externalCode>
            </PicklistOption>
          </salutationNav>
          <lastName>Test Last Name</lastName>
          <firstName>Test First Name</firstName>
          <gender>M</gender>
          <middleName>Test Middle</middleName>
          <preferredName>Test Name</preferredName>
        </PerPersonal>
      </personalInfoNav>
      <nationalIdNav/>
      <personIdExternal>155999</personIdExternal>
      <dateOfBirth>1982-02-01T00:00:00.000</dateOfBirth>
      <employmentNav>
        <EmpEmployment>
          <compInfoNav>
            <EmpCompensation>
              <empCompensationGroupSumCalculatedNav>
                <EmpCompensationGroupSumCalculated>
                  <amount>120000.0</amount>
                </EmpCompensationGroupSumCalculated>
              </empCompensationGroupSumCalculatedNav>
              <startDate>2020-02-01T00:00:00.000</startDate>
            </EmpCompensation>
            <EmpCompensation>
              <empCompensationGroupSumCalculatedNav>
                <EmpCompensationGroupSumCalculated>
                  <amount>132000.0</amount>
                </EmpCompensationGroupSumCalculated>
              </empCompensationGroupSumCalculatedNav>
              <startDate>2020-03-25T00:00:00.000</startDate>
            </EmpCompensation>
            <EmpCompensation>
              <empCompensationGroupSumCalculatedNav>
                <EmpCompensationGroupSumCalculated>
                  <amount>138000.0</amount>
                </EmpCompensationGroupSumCalculated>
              </empCompensationGroupSumCalculatedNav>
              <startDate>2020-03-31T00:00:00.000</startDate>
            </EmpCompensation>
            <EmpCompensation>
              <empCompensationGroupSumCalculatedNav>
                <EmpCompensationGroupSumCalculated>
                  <amount>144000.0</amount>
                </EmpCompensationGroupSumCalculated>
              </empCompensationGroupSumCalculatedNav>
              <startDate>2020-04-10T00:00:00.000</startDate>
            </EmpCompensation>
            <EmpCompensation>
              <empCompensationGroupSumCalculatedNav>
                <EmpCompensationGroupSumCalculated>
                  <amount>150000.0</amount>
                </EmpCompensationGroupSumCalculated>
              </empCompensationGroupSumCalculatedNav>
              <startDate>2020-04-20T00:00:00.000</startDate>
            </EmpCompensation>
            <EmpCompensation>
              <empCompensationGroupSumCalculatedNav>
                <EmpCompensationGroupSumCalculated>
                  <amount>162000.0</amount>
                </EmpCompensationGroupSumCalculated>
              </empCompensationGroupSumCalculatedNav>
              <startDate>2020-06-22T00:00:00.000</startDate>
            </EmpCompensation>
          </compInfoNav>
         <jobInfoNav>
            <EmpJob>
              <costCenter>XS000001</costCenter>
              <standardHours>35.0</standardHours>
              <jobTitle>HR Manager</jobTitle>
              <positionNav>
                <Position>
                  <code>37777</code>
                </Position>
              </positionNav>
              <fte>0.88</fte>
              <startDate>2020-02-01T00:00:00.000</startDate>
            </EmpJob>
            <EmpJob>
              <costCenter>COST0001</costCenter>
              <standardHours>15.0</standardHours>
              <jobTitle>HR Manager</jobTitle>
              <positionNav>
                <Position>
                  <code>37777</code>
                </Position>
              </positionNav>
              <fte>0.38</fte>
              <startDate>2020-03-05T00:00:00.000</startDate>
            </EmpJob>
          </jobInfoNav>
        </EmpEmployment>
      </employmentNav>
    </Person>
  </Person>

Below is the desired result. I tried in javascript something is missing or I am completely wrong.

enter image description here

JavaScript, Java, Groovy, XSLT: which is it? What did you try? — mzjn, Apr 21 '20 at 05:46
Also, how are you expecting to connect the `jobInfoNav` data and the `employmentNav` data? — tim_yates, Apr 21 '20 at 07:33
Creating a row for each `EmpCompensation` would be trivial, but it's not clear why every row shows data from the 1st `EmpJob`. — michael.hor257k, Apr 21 '20 at 07:42
@michael.hor257k person the parent node and the xml tree traverse Person->employmentNav->EmpEmployment->jobInfoNav->EmpJob . Each person record start with and ending — Krishna, Apr 21 '20 at 17:01
@michael.hor257k my bad i have made mistake in the desired result, I have updated the desired result. EmpJob->startdate and EmpCompensation-StartDate is also driving the row in which Job and EmpCompensation or to be added. Please let me know if I am not clear. The volume of data is not huge. — Krishna, Apr 21 '20 at 18:24
@michael.hor257k I really appreciate this, i will build with this approach. — Krishna, Apr 21 '20 at 20:34
@tim_yates jobInfoNav StartDate and Empcompensation startDate the info to be compare for showing the job and compensation in one line personalInfoNav is common data. Please let me know if any information missing. i have updated the desired result. — Krishna, Apr 21 '20 at 21:48
@michael.hor257k trying to format date yyyy-MM-DDT00:00:000 to MM/DD/YYYY. Tried below sytax but it didnt work. — Krishna, Apr 23 '20 at 21:42
I am not familiar with `ms:format-date()`. If you are using XSLT 2.0, then use its own `format-date()` function - see here how: https://www.w3.org/TR/xslt20/#format-date. — michael.hor257k, Apr 23 '20 at 22:00
@michael.hor257k In some cases there could be a chance EmpJob could have more rows than EmpCompensation, hence thought to have two Loop for empjob based and one with Empcompensation based, which will be decided based on the count of empjob and empcompensation. i am reading the count . But not able to use them in IF condition. — Krishna, Apr 30 '20 at 15:49
I think this merits a new question, with a detailed explanation what result you want in each case. — michael.hor257k, Apr 30 '20 at 19:52
@michael.hor257k i have created new question. https://stackoverflow.com/questions/61534678/if-condition-in-xslt-by-comparing-the-count-of-sub-node-in-parent-node — Krishna, Apr 30 '20 at 23:47

score 0 · Accepted Answer · answered Apr 21 '20 at 18:57

I don't intend to write your code for you - only to sketch out how you should approach this using XSLT.

The goal is to produce a row for each EmpCompensation, with common data from the personalInfoNav section, and from the last EmpJob section whose start date is before or on the start date of the current EmpCompensation.

It is assumed that EmpJob elements are listed in chronological order.
It is assumed that your processor supports XSLT 2.0.

The following stylesheet:

XSLT 2.0

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

<xsl:template match="/Person"> 
    <!-- header -->
    <xsl:text>PERNR,Last Name,Cost Center,Job StartDate,Salary EffDate,Annual Salary&#10;</xsl:text>
    <xsl:for-each select="Person">
        <xsl:variable name="personal-data">
            <xsl:value-of select="personIdExternal"/>
            <xsl:text>,</xsl:text>          
            <xsl:value-of select="personalInfoNav/PerPersonal/lastName"/>
            <xsl:text>,</xsl:text>          
        </xsl:variable>
        <xsl:variable name="jobs" select="employmentNav/EmpEmployment/jobInfoNav/EmpJob"/>
        <!-- data rows -->
        <xsl:for-each select="employmentNav/EmpEmployment/compInfoNav/EmpCompensation">
            <!-- personal data -->
            <xsl:copy-of select="$personal-data"/>
            <!-- job data -->
            <xsl:variable name="job" select="$jobs[startDate le current()/startDate][last()]"/>
            <xsl:value-of select="$job/costCenter"/>
            <xsl:text>,</xsl:text>          
            <xsl:value-of select="$job/startDate"/>
            <xsl:text>,</xsl:text>          
            <!-- salary data -->
            <xsl:value-of select="startDate"/>
            <xsl:text>,</xsl:text>          
            <xsl:value-of select="empCompensationGroupSumCalculatedNav/EmpCompensationGroupSumCalculated/amount"/>
            <xsl:text>&#10;</xsl:text>          
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

when applied to your example input, will return:

Result

PERNR,Last Name,Cost Center,Job StartDate,Salary EffDate,Annual Salary
155999,Test Last Name,XS000001,2020-02-01T00:00:00.000,2020-02-01T00:00:00.000,120000.0
155999,Test Last Name,COST0001,2020-03-05T00:00:00.000,2020-03-25T00:00:00.000,132000.0
155999,Test Last Name,COST0001,2020-03-05T00:00:00.000,2020-03-31T00:00:00.000,138000.0
155999,Test Last Name,COST0001,2020-03-05T00:00:00.000,2020-04-10T00:00:00.000,144000.0
155999,Test Last Name,COST0001,2020-03-05T00:00:00.000,2020-04-20T00:00:00.000,150000.0
155999,Test Last Name,COST0001,2020-03-05T00:00:00.000,2020-06-22T00:00:00.000,162000.0

Demo: https://xsltfiddle.liberty-development.net/6pS26mM

XML to CSV - some nodes Repeat and sometimes Not - Java/Groovy/XSLT

1 Answers1