I want to use xslt to transpose my input to generate a header for my csv file while printing associated values in the rows
Here is my sample XMl
<?xml version="1.0" encoding="UTF-8"?>
<rd:Root xmlns:rd="urn:com.report/G2N_Report">
<rd:Row>
<rd:ID>2134</rd:ID>
<rd:Name>Abbi</rd:Name>
<rd:Components>
<rd:Component rd:Descriptor="FIU">2454.12</rd:Component>
<rd:Component rd:Descriptor="Paid TO">504.00</rd:Component>
<rd:Component rd:Descriptor="Imputed Income">0.57</rd:Component>
<rd:Component rd:Descriptor="WHR">1778.71</rd:Component>
<rd:Component rd:Descriptor="THR">2282.71</rd:Component>
<rd:Component rd:Descriptor="Regular">1778.71</rd:Component>
<rd:Component rd:Descriptor="Medical">-69.37</rd:Component>
</rd:Components>
</rd:Row>
<rd:Row>
<rd:ID>2020</rd:ID>
<rd:Name>Abba</rd:Name>
<rd:Components>
<rd:Component rd:Descriptor="Imputed Income">0.21</rd:Component>
<rd:Component rd:Descriptor="WHR">2080.50</rd:Component>
<rd:Component rd:Descriptor="Addnl">228.00</rd:Component>
<rd:Component rd:Descriptor="QBx2.5">641.25</rd:Component>
<rd:Component rd:Descriptor="THR">2308.50</rd:Component>
<rd:Component rd:Descriptor="Sick">228.00</rd:Component>
<rd:Component rd:Descriptor="Regular">2000.71</rd:Component>
</rd:Components>
</rd:Row>
</rd:Root>
My xslt
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:rd="urn:com.report/G2N_Report" exclude-result-prefixes="rd">
<xsl:output method="text" encoding="UTF-8"/>
<xsl:key name="descriptors" match="rd:Component" use="@rd:Descriptor"/>
<!-- Template to match the root element -->
<xsl:template match="/rd:Root">
<!-- Output the CSV header -->
<xsl:text>Employee ID,Name</xsl:text>
<xsl:for-each
select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
<xsl:text>,</xsl:text>
<xsl:value-of select="normalize-space(@rd:Descriptor)"/>
</xsl:for-each>
<xsl:text> </xsl:text>
<!-- Process each Row -->
<xsl:for-each select="rd:Row">
<xsl:value-of select="rd:ID"/>
<xsl:text>,</xsl:text>
<xsl:value-of select="rd:Name"/>
<!-- Process Components -->
<xsl:for-each select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
<xsl:text>,</xsl:text>
<xsl:value-of select="../rd:Component[@rd:Descriptor = current()/@rd:Descriptor]"/>
</xsl:for-each>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Desired Output
Employee ID,Name,FIU,Paid TO,Imputed Income,WHR,THR,Regular,Medical,Addnl,QBx2.5,Sick
2134,Abbi,2454.12,504.00,0.57,1778.71,2282.71,1778.71,-69.37,0,0,0
2020,Abba,0,0,0.21,2080.50,2308.50,2000.71,0,228.00,641.25,228.00
Actual Output
Employee ID,Name,FIU,Paid TO,Imputed Income,WHR,THR,Regular,Medical,Addnl,QBx2.5,Sick
2134,Abbi,2454.12,504.00,0.57,1778.71,2282.71,1778.71,-69.37,228.00,641.25,228.00
2020,Abba,2454.12,504.00,0.57,1778.71,2282.71,1778.71,-69.37,228.00,641.25,228.00
It seems my xslt is consolidating the values and repeating the same with every row. I need the associated values to be printed for each of the component columns and if component doesn't exist within a row then simply 0 should be printed. Please advise.
In your XSLT, when iterating over the rows you have another xsl:for-each
to iterate over the columns and so relative XPath expressions will be from the context node of the selected rd:Component
element. You need to anchor them to the rd:Row
that is being iterated over in the outer for-each. The easiest way to do that is to let a variable and use it in the inner for-each. Additionally, if there is no value and you want to output 0
then you can test if number()
returns a value or NaN
and substitute a default value of 0
:
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:rd="urn:com.report/G2N_Report" exclude-result-prefixes="rd">
<xsl:output method="text" encoding="UTF-8"/>
<xsl:key name="descriptors" match="rd:Component" use="@rd:Descriptor"/>
<!-- Template to match the root element -->
<xsl:template match="/rd:Root">
<!-- Output the CSV header -->
<xsl:text>Employee ID,Name</xsl:text>
<xsl:for-each
select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
<xsl:text>,</xsl:text>
<xsl:value-of select="normalize-space(@rd:Descriptor)"/>
</xsl:for-each>
<xsl:text> </xsl:text>
<!-- Process each Row -->
<xsl:for-each select="rd:Row">
<xsl:value-of select="rd:ID"/>
<xsl:text>,</xsl:text>
<xsl:value-of select="rd:Name"/>
<xsl:variable name="row" select="."/>
<!-- Process Components -->
<xsl:for-each select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
<xsl:text>,</xsl:text>
<xsl:variable name="value" select="$row/rd:Components/rd:Component[@rd:Descriptor = current()/@rd:Descriptor]"/>
<xsl:choose>
<xsl:when test="number($value)">
<xsl:value-of select="$value"/>
</xsl:when>
<xsl:otherwise>0</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>`