Multiple rows using XSLT

Question

I appreciate if anyone could help me on this. I have an XML that looks like this:

<root>
<worker>
     <Primary_Key>12345</Primary_Key>
     <First_Name>ABC</First_Name>
     <Last_Name>DEF</Last_Name>         
    <Multiple_Data>
        <Code>MO</Code>
        <Amount>35</Amount>
    </Multiple_Data>
    <Multiple_Data>
        <Code>PA</Code>
        <Amount>45</Amount>
    </Multiple_Data>
</worker>
<worker>
    <Primary_Key>67890</Primary_Key>
    <First_Name>GHI</First_Name>
    <Last_Name>JKL</Last_Name>
    <Multiple_Data>
        <Code>PA</Code>
        <Amount>25</Amount>
    </Multiple_Data>
</worker>
<worker>
    <Primary_Key>11121</Primary_Key>
    <First_Name>MNO</First_Name>
    <Last_Name>PQR</Last_Name>    
</worker</root>

And I need to have the output that will exactly looks like this:

12345,ABC,DEF,MO,35
12345,ABC,DEF,PA,45
67890,GHI,JKL,PA,25
11121,MNO,PQR,,

12345 has multiple data Codes and Amounts. Values are displayed on separate rows. 11121 doesn't have code and amount so the delimiter ,, is the only one displayed.

Here's the XSLT but seems not working:

<output method="text"/>

<variable name="delimiter"><text>,</text></variable>    
<variable name="linefeed" select="'&#xd;&#xa;'"/>
<template match="child::Primary_Key">     
    <choose>
        <when test="preceding-sibling::Multiple_Data//child::text() = ''">
            <for-each select="following-sibling::Multiple_Data">

                <value-of select="concat(preceding-sibling::Primary_Key/child::text(), $delimiter, preceding-sibling::First_Name/child::text(), $delimiter, preceding-sibling::Last_Name/child::text(), $delimiter, child::Code/child::text(), $delimiter, child::Amount/child::text())"/>

                <if test="(self::Primary_Key[position() = last()])">
                    <value-of select="$linefeed"/> 
                </if>

            </for-each>
        </when>
        <otherwise>
            <for-each select="child::Primary_Key">                    
                <value-of select="concat(preceding-sibling::Primary_Key/child::text(), $delimiter, preceding-sibling::First_Name/child::text(), $delimiter, preceding-sibling::Last_Name/child::text(), $delimiter, $delimiter )"/>                
                <if test="(self::Primary_Key[position() = last()])">
                    <value-of select="$linefeed"/> 
                </if>

            </for-each>

        </otherwise>

    </choose>                        
</template>
<template match="child::root/worker">
    <apply-templates select="child::Primary_Key"/>
</template>
</transform>

When I try to use this below XSLT, it's giving me unnecessary new line at the top and spaces. if(position() > 1) is not working to get rid the first blank line in my output. And of course, worker who doesn't have multiple data is not displayed here.

<transform xmlns="http://www.w3.org/1999/XSL/Transform" version="1.0">

<output method="text"/>

<variable name="delimiter"><text>,</text></variable>    
<variable name="linefeed" select="'&#xd;&#xa;'"/>
<template match="child::Primary_Key">     

       <for-each select="following-sibling::Multiple_Data">

                <value-of select="concat(preceding-sibling::Primary_Key/child::text(), $delimiter, preceding-sibling::First_Name/child::text(), $delimiter, preceding-sibling::Last_Name/child::text(), $delimiter, child::Code/child::text(), $delimiter, child::Amount/child::text())"/>                
                <if test="(self::Primary_Key[position() = last()])">
                    <value-of select="$linefeed"/> 
                </if>

            </for-each>

</template>
<template match="child::root/worker">
    <apply-templates select="child::Primary_Key"/>
</template>

Are there any other better ways to code this without using this transform in the heading?

<transform xmlns="http://www.w3.org/1999/XSL/Transform" version="1.0">

I'd like to create the xslt starting with:

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

but looks like the child, preceding-sibling and following-sibling:: codes are not supported by that.

Thanks a lot for your help.

Answer 1

Given that you can use XSLT 2 and want to have some output for data that does not exist in the input sample I would suggest to use a two step transformation where the first step in a separate mode simply adds empty elements for the missing data and then the second step in the default mode outputs the CSV.

If you can use Saxon 9.8 (any edition) or 9.7 PE/EE in oXygen and XSLT 3 you can easily set up the other mode with

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="3.0">

  <xsl:output method="text"/>

  <xsl:mode name="add-dummy-data" on-no-match="shallow-copy"/>

  <xsl:variable name="dummy-data">
      <xsl:apply-templates mode="add-dummy-data"/>
  </xsl:variable>

  <xsl:template match="worker[not(Multiple_Data)]" mode="add-dummy-data">
      <xsl:copy>
          <xsl:copy-of select="@*, node()"/>
          <Multiple_Data>
              <Code/>
              <Amount/>
          </Multiple_Data>
      </xsl:copy>
  </xsl:template>

  <xsl:template match="/">
    <xsl:apply-templates select="$dummy-data/root/worker/Multiple_Data"/>
  </xsl:template>

  <xsl:template match="Multiple_Data">
      <xsl:value-of select="../Primary_Key, ../First_Name, ../Last_Name, Code, Amount" separator=","/>
      <xsl:text>&#10;</xsl:text>
  </xsl:template>

</xsl:stylesheet>

see https://xsltfiddle.liberty-development.net/bdxtq1/1

if you are tied to XSLT 2 you need to use

<xsl:template match="@* | node()" mode="add-dummy-data">
  <xsl:copy>
    <xsl:apply-templates select="@* | node()" mode="#current"/>
  </xsl:copy>
</xsl:template>

instead of <xsl:mode name="add-dummy-data" on-no-match="shallow-copy"/>