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

Question

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>

Do you have a specific question or difficulty? Or are you just looking for someone to do your work for you? — michael.hor257k
– michael.hor257k, Commented Jun 3, 2019 at 18:35

zx485 · Accepted Answer · 2019-06-03 19:05:29Z

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>

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related