Search code examples
xmlxsltxslt-2.0xslt-grouping

XSLT code - Create a new XML segment for every 3 xml segments

I'm still learning and enhancing my XSLT skills. But hope it's okay if i can see from experts regarding my requirement.

For every 3 records xml segment, a new record xml segment will be created position on top which will sum the amount of the 3 records. Then, a new record (PK50) segment will still be produced in excess or the remaining line items. Kindly see the sample data below. There are PK50 records created, where the last one contains the summation of the remaining 2 records.

PostingKey 50 indicates the new record created which contains the amount summation of every 3 records. The Account field will have a value "Summation" as well. Appreciate any inputs from your end. Thank you so much!

Source data:

<?xml version="1.0" encoding="UTF-8"?>
<ns1:AccrualReport xmlns:ns1="http://client.com/expense/expensereport">
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>lines</Account>
        <Amt_DocCurr>12.5</Amt_DocCurr>
        <Amt_LocCurr>12.5</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>lines</Account>
        <Amt_DocCurr>22</Amt_DocCurr>
        <Amt_LocCurr>22</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1.15</Amt_DocCurr>
        <Amt_LocCurr>1.15</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>20</Amt_DocCurr>
        <Amt_LocCurr>20</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1</Amt_DocCurr>
        <Amt_LocCurr>1</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>2</Amt_DocCurr>
        <Amt_LocCurr>2</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1</Amt_DocCurr>
        <Amt_LocCurr>1</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1</Amt_DocCurr>
        <Amt_LocCurr>1</Amt_LocCurr>
    </Record>
</ns1:AccrualReport>

Output Expected data:

<?xml version="1.0" encoding="UTF-8"?>
<ns1:AccrualReport xmlns:ns1="http://client.com/expense/expensereport">
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>50</PostingKey>
        <Account>Summation</Account>
        <Amt_DocCurr>35.65</Amt_DocCurr>
        <Amt_LocCurr>35.65</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>lines</Account>
        <Amt_DocCurr>12.5</Amt_DocCurr>
        <Amt_LocCurr>12.5</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>lines</Account>
        <Amt_DocCurr>22</Amt_DocCurr>
        <Amt_LocCurr>22</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1.15</Amt_DocCurr>
        <Amt_LocCurr>1.15</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>50</PostingKey>
        <Account>Summation</Account>
        <Amt_DocCurr>23</Amt_DocCurr>
        <Amt_LocCurr>23</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>20</Amt_DocCurr>
        <Amt_LocCurr>20</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1</Amt_DocCurr>
        <Amt_LocCurr>1</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>2</Amt_DocCurr>
        <Amt_LocCurr>2</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>50</PostingKey>
        <Account>Summation</Account>
        <Amt_DocCurr>2</Amt_DocCurr>
        <Amt_LocCurr>2</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1</Amt_DocCurr>
        <Amt_LocCurr>1</Amt_LocCurr>
    </Record>
    <Record>
        <CoCode>BI10</CoCode>
        <DocDate>25/01/2022</DocDate>
        <PostDate>25/01/2022</PostDate>
        <DocType>SB</DocType>
        <PostingKey>40</PostingKey>
        <Account>60875000</Account>
        <Amt_DocCurr>1</Amt_DocCurr>
        <Amt_LocCurr>1</Amt_LocCurr>
    </Record>
</ns1:AccrualReport>

So far, here's the current code which I am using. The only problem is the summation of the amount in the Amt_DocCurr and Amt_LocCurr. Appreciate any inputs to handle this and incorporate in my current code.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" indent="yes"/>
   <xsl:param name="rows" select="3"/>
    <xsl:template match="/*">
        <xsl:apply-templates select="Record[position() mod $rows = 1]"/>
    </xsl:template>
    <xsl:template match="Record">
        <Record>
            <CoCode><xsl:value-of select="CoCode"/></CoCode>
            <DocDate><xsl:value-of select="DocDate"/></DocDate>
            <PostDate><xsl:value-of select="PostDate"/></PostDate>
            <DocType><xsl:value-of select="DocType"/></DocType>
            <PostingKey>50</PostingKey>
            <Account>Summation</Account>
            <Amt_DocCurr><xsl:value-of select="Amt_DocCurr"/></Amt_DocCurr>
            <Amt_LocCurr><xsl:value-of select="Amt_LocCurr"/></Amt_LocCurr> 
        </Record>
        <xsl:copy-of select=".|following-sibling::Record[not(position() > $rows -1)]"/>
    </xsl:template>

    <xsl:template match="/">
        <ns1:AccrualReport xmlns:ns1="http://client.com/expense/expensereport">
            <xsl:apply-templates />
        </ns1:AccrualReport>
    </xsl:template>

</xsl:stylesheet>
Solution

  • Here is a way this could be done :

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

  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/">
    <AccrualReportOutput>
      <xsl:apply-templates select="AccrualReport/Record[position() mod 2 = 0]"/>
    </AccrualReportOutput>
  </xsl:template>
  
  <xsl:template match="Record">
    <xsl:variable name="group" select=". | preceding-sibling::Record[1]"/>
    <!-- Create new Summation Record -->
    <xsl:copy>
      <xsl:copy-of select="* except (Account, PostingKey, Amt_DocCurr, Amt_LocCurr)"/>
      <Account>Summation</Account>
      <PostingKey>50</PostingKey>
      <Amt_DocCurr><xsl:value-of select="sum($group/Amt_DocCurr)"/></Amt_DocCurr>
      <Amt_LocCurr><xsl:value-of select="sum($group/Amt_LocCurr)"/></Amt_LocCurr>
    </xsl:copy>
    <!-- Copy 2 Records of group -->
    <xsl:copy-of select="$group"/>
  </xsl:template>
  
</xsl:stylesheet>

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

    (I have removed your ns1: namespace prefix, not knowing what that namespace is, being undeclared in your XML.)