CODEWITHSUNDEEP
xmlxslttranspose
user2784820
user2784820

Reputation: 875

Need help to transpose XML from Rows Into Elements in XML output

I need help to transpose XML from Rows Into Elements in XML output. I listed below the source and expected XMLs. There can be multiple instances of <Pay_Result_Lines_group> with most just have <Tax_Order> of 99. The output XML is sorted based on <Tax_Order> that would be 1-4 or 99. For those instances with order 99, they will be put under <Tax_5_Desc>, <Tax_6_desc>, <Tax_7_Desc>,... <Tax_n_Desc> till how many of them in source XML.

The source XML is below:

    <Report_Entry>
    <Account_ID>123456</Account_ID>
    <Pay_Result_Lines_group>
        <Tax_N_Description>W_FWFEDERAL</Tax_N_Description>
        <Tax_N_Rate>0</Tax_N_Rate>
        <Tax_Order>1</Tax_Order>
    </Pay_Result_Lines_group>
    <Pay_Result_Lines_group>
        <Tax_N_Description>W_MEDFEDERAL</Tax_N_Description>
        <Tax_N_Rate>0.0145</Tax_N_Rate>
        <Tax_Order>2</Tax_Order>
    </Pay_Result_Lines_group>
    <Pay_Result_Lines_group>
        <Tax_N_Description>W_OASFEDERAL</Tax_N_Description>
        <Tax_N_Rate>0.062</Tax_N_Rate>
        <Tax_Order>4</Tax_Order>
    </Pay_Result_Lines_group>
    <Pay_Result_Lines_group>
        <Tax_N_Description>W_SUI34</Tax_N_Description>
        <Tax_N_Rate>0.003825</Tax_N_Rate>
        <Tax_Order>99</Tax_Order>
    </Pay_Result_Lines_group>
    <Pay_Result_Lines_group>
        <Tax_N_Description>W_NJFAMNJFLI</Tax_N_Description>
        <Tax_N_Rate>0.0008</Tax_N_Rate>
        <Tax_Order>99</Tax_Order>
    </Pay_Result_Lines_group>
    <Pay_Result_Lines_group>
        <Tax_N_Description>W_NJWDNJ-WDF</Tax_N_Description>
        <Tax_N_Rate>0.000425</Tax_N_Rate>
        <Tax_Order>99</Tax_Order>
    </Pay_Result_Lines_group>
</Report_Entry>

The expected output is below:

    <Workers>
    <Worker>
        <Account_ID>123456</Account_ID>
        <Tax_1_Desc>W_FWFEDERAL</Tax_1_Desc>
        <Tax_1_Rate/>
        <Tax_2_Desc>W_MEDFEDERAL</Tax_2_Desc>
        <Tax_2_Rate/>
        <Tax_4_Desc>W_OASFEDERAL</Tax_4_Desc>
        <Tax_4_Rate/>
        <Tax_5_Desc>W_SUI34</Tax_5_Desc>
        <Tax_5_Rate/>
        <Tax_6_Desc>W_NJFAMNJFLI</Tax_6_Desc>
        <Tax_6_Rate/>
        <Tax_7_Desc>W_NJWDNJ-WDF</Tax_7_Desc>
        <Tax_7_Rate/>
    </Worker>
</Workers>

Upvotes: 0

Views: 179

Answers (1)

zx485
zx485

Reputation: 29022

You can use this XSLT-1.0 stylesheet:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" omit-xml-declaration="yes" encoding="UTF-8" indent="yes" />

    <xsl:template match="/Report_Entry">
        <Workers>
            <Worker>
                <xsl:copy-of select="Account_ID" />
                <xsl:apply-templates select="Pay_Result_Lines_group" />
            </Worker>
        </Workers>
    </xsl:template>

    <xsl:template match="Pay_Result_Lines_group">
        <xsl:variable name="nbr">
            <xsl:choose>
                <xsl:when test="5 > Tax_Order">
                    <xsl:value-of select="Tax_Order" />
                </xsl:when>
                <xsl:when test="Tax_Order = 99">
                    <xsl:value-of select="preceding-sibling::Pay_Result_Lines_group[Tax_Order != 99][1]/Tax_Order + position() - count(preceding-sibling::Pay_Result_Lines_group[Tax_Order != 99])" />
                </xsl:when>
            </xsl:choose>
        </xsl:variable>
        <xsl:element name="{concat('Tax_',$nbr,'_Desc')}">
            <xsl:value-of select="Tax_N_Description" />
        </xsl:element>
        <xsl:element name="{concat('Tax_',$nbr,'_Rate')}">
            <!-- <xsl:value-of select="Tax_N_Rate" /> -->
        </xsl:element>
    </xsl:template>

</xsl:stylesheet>

Its output is: 

<Workers>
  <Worker>
    <Account_ID>123456</Account_ID>
    <Tax_1_Desc>W_FWFEDERAL</Tax_1_Desc>
    <Tax_1_Rate/>
    <Tax_2_Desc>W_MEDFEDERAL</Tax_2_Desc>
    <Tax_2_Rate/>
    <Tax_4_Desc>W_OASFEDERAL</Tax_4_Desc>
    <Tax_4_Rate/>
    <Tax_5_Desc>W_SUI34</Tax_5_Desc>
    <Tax_5_Rate/>
    <Tax_6_Desc>W_NJFAMNJFLI</Tax_6_Desc>
    <Tax_6_Rate/>
    <Tax_7_Desc>W_NJWDNJ-WDF</Tax_7_Desc>
    <Tax_7_Rate/>
  </Worker>
</Workers>

Upvotes: 1

Related Questions