XSLT - XML to CSV transpose columns to rows with two different type of address

Question

Following is my xml

 <?xml version='1.0' encoding='UTF-8'?>
<Report_Data>
    <Report_Entry>
        <PERSON>12345</PERSON>
        <NAME> Person Name </NAME>
        <Emergency_Contacts_group>
            <ADDRESS_LINE_1>Emergency contact address 1</ADDRESS_LINE_1>
            <ADDRESS_LINE_2>Emergency contact address 2</ADDRESS_LINE_2>
            <ADDRESS_LINE_3>Emergency contact address 3</ADDRESS_LINE_3>
            <ADDRESS_LINE_4>Emergency contact address 4</ADDRESS_LINE_4>
        </Emergency_Contacts_group>

        <Trustee_group>
            <TRUST_ADDRESS_LINE_1>TRUSTEE address 1</TRUST_ADDRESS_LINE_1>
            <TRUST_ADDRESS_LINE_2>TRUSTEE address 2</TRUST_ADDRESS_LINE_2>
            <TRUST_ADDRESS_LINE_3>TRUSTEE address 3</TRUST_ADDRESS_LINE_3>
        </Trustee_group>
    </Report_Entry>
    </Report_Data>

Requirement is to get output in following format:

PERSON|NAME|ADDRESS_LINE_TYPE|ADDRESS_LINE_DATA|TRUSTEE_ADDRESS_LINE_TYPE|TRUSTEE_ADDRESS_DATA
12345|Person Name|ADDRESS_LINE_1|Emergency contact address 1|ADDRESS_LINE_1|TRUSTEE address 1
12345|Person Name|ADDRESS_LINE_2|Emergency contact address 2|ADDRESS_LINE_2|TRUSTEE address 2
12345|Person Name|ADDRESS_LINE_3|Emergency contact address 3|ADDRESS_LINE_3|TRUSTEE address 3
12345|Person Name|ADDRESS_LINE_4|Emergency contact address 4||

I have written XSLT it has problem

    <?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"
    version="1.0">

  <xsl:output method="text"/>

  <xsl:template match="/">
    <xsl:text>Person|Address_LINE_TYPE| ADDRESS_LINE_DATA</xsl:text>
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="*[contains(name(),'ADDRESS_LINE')]">
      <xsl:text>&#xa;</xsl:text>
      <xsl:value-of select="../../PERSON"/>
      <xsl:text>|</xsl:text>
      <xsl:value-of select="local-name()"/>
      <xsl:text>|</xsl:text>
      <xsl:value-of select="."/> 

  </xsl:template>

  <xsl:template match="text()"/>

</xsl:stylesheet>

which is producing output like this which is wrong:

Person|Address_LINE_TYPE| ADDRESS_LINE_DATA
12345|ADDRESS_LINE_1|Emergency contact address 1
12345|ADDRESS_LINE_2|Emergency contact address 2
12345|ADDRESS_LINE_3|Emergency contact address 3
12345|ADDRESS_LINE_4|Emergency contact address 4
12345|TRUST_ADDRESS_LINE_1|TRUSTEE address 1
12345|TRUST_ADDRESS_LINE_2|TRUSTEE address 2
12345|TRUST_ADDRESS_LINE_3|TRUSTEE address 3

Problem:1 Trustee_Group data is printing below the emergency contact.

Problem:2 need separate column for Trustee_group address line type and it should ADDRESS_LINE_1 and not the TRUST_ADDRESS_LINE_1

Please guide me to achieve the desired output.

Answer 1

Here's how I would do it, assuming there is always an "ADDRESS_LINE_X" and there might or might not be an associated "TRUST_ADDRESS_LINE_X".

<?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"
    version="1.0">

  <xsl:output method="text"/>

  <xsl:template match="/">
    <xsl:text>PERSON|NAME|ADDRESS_LINE_TYPE|ADDRESS_LINE_DATA|TRUSTEE_ADDRESS_LINE_TYPE|TRUSTEE_ADDRESS_DATA</xsl:text>
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="Emergency_Contacts_group">
      <xsl:for-each select="*">
        <xsl:text>&#xa;</xsl:text>
        <xsl:value-of select="../../PERSON"/>
        <xsl:text>|</xsl:text>
        <xsl:value-of select="../../NAME"/>
        <xsl:text>|</xsl:text>
        <xsl:value-of select="local-name()"/>
        <xsl:text>|</xsl:text>
        <xsl:value-of select="."/>
        <xsl:text>|</xsl:text>
            <xsl:variable name="trustName" select="concat('TRUST_',local-name())"/>
            <xsl:variable name="trust" select="../../Trustee_group/*[local-name()=$trustName]"/>
        <xsl:choose>
            <xsl:when test="$trust">
                <xsl:value-of select="$trustName"/>
                <xsl:text>|</xsl:text>
                <xsl:value-of select="$trust"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:text>|</xsl:text>        
            </xsl:otherwise>
        </xsl:choose>

      </xsl:for-each>
  </xsl:template>

  <xsl:template match="text()"/>

</xsl:stylesheet>

See it working here : https://xsltfiddle.liberty-development.net/gVhDDyT/1

Answer 2

This is not exactly trivial. Here's how I would approach it:

XSLT 1.0

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

<xsl:template match="/Report_Data">
    <xsl:text>PERSON|NAME|ADDRESS_LINE_TYPE|ADDRESS_LINE_DATA|TRUSTEE_ADDRESS_LINE_TYPE|TRUSTEE_ADDRESS_DATA&#10;</xsl:text>
    <xsl:for-each select="Report_Entry">
        <xsl:call-template name="generate-rows">
            <xsl:with-param name="person-data">
                <xsl:value-of select="PERSON"/>
                <xsl:text>|</xsl:text>
                <xsl:value-of select="NAME"/>
                <xsl:text>|</xsl:text>
            </xsl:with-param>
            <xsl:with-param name="emergency-contacts" select="Emergency_Contacts_group/*"/>
            <xsl:with-param name="trustees" select="Trustee_group/*"/>
        </xsl:call-template>
    </xsl:for-each>
</xsl:template>

<xsl:template name="generate-rows">
    <xsl:param name="person-data"/>
    <xsl:param name="emergency-contacts"/>
    <xsl:param name="trustees"/>
    <xsl:param name="i" select="1"/>
    <xsl:variable name="emergency-contact" select="$emergency-contacts[$i]" />
    <xsl:variable name="trustee" select="$trustees[$i]" />
    <!-- write to output -->
    <xsl:value-of select="$person-data"/>
    <xsl:value-of select="name($emergency-contact)"/>
    <xsl:text>|</xsl:text>  
    <xsl:value-of select="$emergency-contact"/>
    <xsl:text>|</xsl:text>  
    <xsl:value-of select="name($trustee)"/>
    <xsl:text>|</xsl:text>  
    <xsl:value-of select="$trustee"/>
    <xsl:text>&#10;</xsl:text>
    <!-- recursive call -->
    <xsl:if test="$i &lt; count($emergency-contacts) or $i &lt; count($trustee)">
        <xsl:call-template name="generate-rows">
            <xsl:with-param name="person-data" select="$person-data"/>
            <xsl:with-param name="emergency-contacts" select="$emergency-contacts"/>
            <xsl:with-param name="trustees" select="$trustees"/>
            <xsl:with-param name="i" select="$i + 1"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

</xsl:stylesheet>