xslt compare two different nodes and then combine

问题:

I have a requirement where i need to show the difference amount (<TotalAmount>) based on two different values which is in different section.The input xml as below.

 <TXLife xmlns="http://ACORD.org/Standards/Life/2">
    <TXLifeRequest>
        <FundCode>LTRW00</FundCode>
        <AccountNumber>34142</AccountNumber>
        <ReversalInd>Cr</ReversalInd>
        <TotalAmount>1600</TotalAmount>
    </TXLifeRequest>
    <TXLifeRequest>
        <FundCode>LTRW00</FundCode>
        <AccountNumber>34142</AccountNumber>
        <ReversalInd>Dr</ReversalInd>
        <TotalAmount>350</TotalAmount>
    </TXLifeRequest>
    <TXLifeRequest>
        <FundCode>LUL500</FundCode>
        <AccountNumber>34142</AccountNumber>
        <ReversalInd>Cr</ReversalInd>
        <TotalAmount>500</TotalAmount>
    </TXLifeRequest>
    <TXLifeRequest>
        <FundCode>LUL500</FundCode>
        <AccountNumber>34142</AccountNumber>
        <ReversalInd>Dr</ReversalInd>
        <TotalAmount>800</TotalAmount>
    </TXLifeRequest>    
</TXLife>

From the above xml the criteria to find difference amount is <FundCode> and <AccountNumber>. If any sections having same <FundCode> and <AccountNumber> retrieve <TotalAmount> and find the difference.

for example from above xml:-

There are two sections with the same <fundcode> and <Accountnumber> with LTRW00 and 34142. Now the difference of <TotalAmount> is 1250 (1600 - 250). I need to repeat this logic on other sections also.

So the final output xml should like this:

 <TXLife xmlns="http://ACORD.org/Standards/Life/2">
        <TXLifeRequest>
            <FundCode>LTRW00</FundCode>
            <AccountNumber>34142</AccountNumber>
            <ReversalInd>Cr</ReversalInd>
            <TotalAmount>1250</TotalAmount>
        </TXLifeRequest>
        <TXLifeRequest>
            <FundCode>LUL500</FundCode>
            <AccountNumber>34142</AccountNumber>
            <ReversalInd>Dr</ReversalInd>
            <TotalAmount>300</TotalAmount>
        </TXLifeRequest>    
    </TXLife>

And also if you observe the <ReversalInd> that is CR/DR should be identified based on highest TotalAmount value.

I have applied the xslt below but no output. Any ideas how to implement in xslt 1.0. Greatly appreciated.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0" xmlns:ns="http://ACORD.org/Standards/Life/2">
    <xsl:output omit-xml-declaration="yes" indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="node()|@*">
        <xsl:copy>
            <xsl:apply-templates select="node()|@*"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="/ns:TXLife/ns:TXLifeRequest">
        <xsl:element name="TXLife" namespace="http://ACORD.org/Standards/Life/2">
           <xsl:call-template name="balance">
               <xsl:with-param name="total" select="ns:TotalAmount"></xsl:with-param>
           </xsl:call-template>
            <xsl:copy-of select="."/>
        </xsl:element>
    </xsl:template>

    <xsl:template name="balance">
        <xsl:param name="total"></xsl:param>
        <xsl:variable name="reminder" select="0"></xsl:variable>        
        <xsl:variable name="val1">
        <xsl:value-of select="$total[1]"/>
        </xsl:variable>
        <xsl:variable name="val2">
        <xsl:value-of select="$total[position() &gt; 1]"/>
        </xsl:variable>

        <xsl:if test="$val1 &gt; $val2">
            <remainingAmount><xsl:value-of select="$val1 - $val2"/></remainingAmount>
        </xsl:if>

    </xsl:template>
</xsl:stylesheet>

回答1:

Try following solution based on Muenchian grouping. The TXLifeRequest are grouped by FundCode and AccountNumber.

It shout work even if there are more than two entries in the group. All Data for a group output (especial ReversalInd) is from the one with the highest TotalAmount. The value of TotalAmount is the difference of the first (highest) TotalAmount and the remaining ones.

It also considers the request: "And also if you observe the that is CR/DR should be identified based on highest TotalAmount value."

xsl:stylesheet version="1.0" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ns="http://ACORD.org/Standards/Life/2">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>

    <xsl:key name="kTXLifeRequest" match="ns:TXLifeRequest" use="concat(ns:FundCode,'#',ns:AccountNumber)"/>

    <xsl:template match="node()|@*">

        <xsl:copy>
            <xsl:apply-templates select="node()|@*"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="/*">
        <xsl:copy>

            <xsl:for-each select=
                 "ns:TXLifeRequest[generate-id() = generate-id(key('kTXLifeRequest',concat(ns:FundCode,'#',ns:AccountNumber))[1])]" >
                <xsl:copy>
                    <xsl:variable name="group"
                                  select="key('kTXLifeRequest',concat(current()/ns:FundCode,'#',current()/ns:AccountNumber))" />
                    <xsl:for-each select= "$group" >
                        <xsl:sort select="ns:TotalAmount" data-type="number" order="descending"/>
                        <xsl:if test="position() = 1">
                            <xsl:apply-templates select="*[local-name() != 'TotalAmount']" />
                            <TotalAmount>
                                <xsl:value-of select="ns:TotalAmount -  sum($group/ns:TotalAmount)+ ns:TotalAmount" />
                            </TotalAmount>
                        </xsl:if>
                    </xsl:for-each>
                </xsl:copy>
            </xsl:for-each>
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>

Which will generate following output:

<TXLife xmlns="http://ACORD.org/Standards/Life/2">
  <TXLifeRequest>
    <FundCode>LTRW00</FundCode>
    <AccountNumber>34142</AccountNumber>
    <ReversalInd>Cr</ReversalInd>
    <TotalAmount xmlns="">1250</TotalAmount>
  </TXLifeRequest>
  <TXLifeRequest>
    <FundCode>LUL500</FundCode>
    <AccountNumber>34142</AccountNumber>
    <ReversalInd>Dr</ReversalInd>
    <TotalAmount xmlns="">300</TotalAmount>
  </TXLifeRequest>
</TXLife>

Update for additional request to calculate a GrandTotal:

<xsl:stylesheet version="1.0" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ns="http://ACORD.org/Standards/Life/2">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>

    <xsl:key name="kTXLifeRequest" match="ns:TXLifeRequest" use="concat(ns:FundCode,'#',ns:AccountNumber)" />

    <xsl:template match="node()|@*">

        <xsl:copy>
            <xsl:apply-templates select="node()|@*"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="/*">
        <xsl:copy>
            <xsl:for-each select=
                 "ns:TXLifeRequest[generate-id() = generate-id(key('kTXLifeRequest',concat(ns:FundCode,'#',ns:AccountNumber))[1])]" >
                <xsl:copy>
                    <xsl:variable name="group"
                                  select="key('kTXLifeRequest',concat(current()/ns:FundCode,'#',current()/ns:AccountNumber))" />
                    <xsl:for-each select= "$group" >
                        <xsl:sort select="ns:TotalAmount" data-type="number" order="descending"/>
                        <xsl:if test="position() = 1">

                            <xsl:apply-templates select="*[local-name() != 'TotalAmount']" />
                            <TotalAmount>
                                <xsl:value-of select="ns:TotalAmount -  sum($group/ns:TotalAmount)+ ns:TotalAmount" />
                            </TotalAmount>
                        </xsl:if>
                    </xsl:for-each>
                </xsl:copy>

            </xsl:for-each>
            <GrandTotal>
                <xsl:call-template name="totalSum">
                    <xsl:with-param name="groups"
                        select=
                        "ns:TXLifeRequest[generate-id() = generate-id(key('kTXLifeRequest',concat(ns:FundCode,'#',ns:AccountNumber))[1])]" />
            </xsl:call-template>
            </GrandTotal>
        </xsl:copy>
    </xsl:template>

    <xsl:template name="totalSum">
        <xsl:param name="groups" />
        <xsl:param name="gpos" select="1"/>
        <xsl:param name="sum" select="0" />
        <xsl:choose>
            <xsl:when test="$gpos &lt;= count($groups)" >
                    <xsl:variable name="group"
                                  select="key('kTXLifeRequest',concat($groups[$gpos]/ns:FundCode,'#',$groups[$gpos]/ns:AccountNumber))" />
                    <xsl:for-each select= "$group" >
                        <xsl:sort select="ns:TotalAmount" data-type="number" order="descending"/>
                        <xsl:if test="position() = 1">
                            <xsl:variable name="actTotal" select="ns:TotalAmount -  sum($group/ns:TotalAmount)+ ns:TotalAmount" />
                            <xsl:call-template name="totalSum">
                                <xsl:with-param name="groups" select="$groups" />
                                <xsl:with-param name ="gpos" select="$gpos + 1" />
                                <xsl:with-param name="sum" select="$sum + $actTotal" />
                            </xsl:call-template>
                        </xsl:if>
                    </xsl:for-each>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$sum"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

</xsl:stylesheet>

回答2:

This is your solution to sum these values:

XSLT:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.1"
  xmlns:ns="http://ACORD.org/Standards/Life/2">
  <xsl:output omit-xml-declaration="yes" indent="yes"/>
  <xsl:strip-space elements="*"/>
  <xsl:key name="Request" match="ns:TXLifeRequest" use="ns:FundCode"/>


  <xsl:template match="ns:TXLife">
    <TXLife>
      <xsl:variable name="result"><xsl:apply-templates
        select="ns:TXLifeRequest[generate-id() = generate-id(key('Request',ns:FundCode))]"/>
      </xsl:variable>
      <xsl:copy-of select="$result"/>
      <TXLifeRequest><xsl:element name="GrandTotal"><xsl:value-of select="sum($result//TotalAmount)"/></xsl:element></TXLifeRequest>
    </TXLife>
  </xsl:template>

  <xsl:template match="ns:TXLifeRequest">
    <TXLifeRequest>
      <xsl:for-each select="*">
        <xsl:choose>
          <xsl:when test="name()='TotalAmount'">
            <xsl:variable name="currentFundCode" select="preceding-sibling::ns:FundCode"/>
            <xsl:variable name="currentAccountNumber" select="preceding-sibling::ns:AccountNumber"/>
            <xsl:variable name="amountToBeDeduct"
              select="parent::ns:TXLifeRequest/following-sibling::ns:TXLifeRequest[ns:FundCode=$currentFundCode and ns:AccountNumber=$currentAccountNumber]/ns:TotalAmount/text()"/>
            <xsl:variable name="actualAmt" select=". - $amountToBeDeduct"/>
            <xsl:element name="{name()}">
              <xsl:choose>
                <xsl:when test="starts-with($actualAmt,'-')">
                  <xsl:value-of select="substring-after($actualAmt,'-')"/>
                </xsl:when>
                <xsl:otherwise>
                  <xsl:value-of select="$actualAmt"/>
                </xsl:otherwise>
              </xsl:choose>
            </xsl:element>
          </xsl:when>
          <xsl:otherwise>
            <xsl:element name="{name()}">
              <xsl:value-of select="."/>
            </xsl:element>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:for-each>
    </TXLifeRequest>
  </xsl:template>


</xsl:stylesheet>

OUTPUT:

<TXLife xmlns:ns="http://ACORD.org/Standards/Life/2">
   <TXLifeRequest>
      <FundCode>LTRW00</FundCode>
      <AccountNumber>34142</AccountNumber>
      <ReversalInd>Cr</ReversalInd>
      <TotalAmount>1250</TotalAmount>
   </TXLifeRequest>
   <TXLifeRequest>
      <FundCode>LUL500</FundCode>
      <AccountNumber>34142</AccountNumber>
      <ReversalInd>Cr</ReversalInd>
      <TotalAmount>300</TotalAmount>
   </TXLifeRequest>
   <TXLifeRequest>
      <GrandTotal>1550</GrandTotal>
   </TXLifeRequest>
</TXLife>