CODEWITHSUNDEEP
xmlxsltxslt-1.0
Maverick Riz
Maverick Riz

Reputation: 2065

XSLT how to merge based on an element

I have the below input XML:

<Fees>
<user>
    <value>userA</value>
</user>
<feeList>
    <userFee>
        <owner>
            <Id>owner1</Id>
        </owner>
        <Amount>
            <sum>100</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner1</Id>
        </owner>
        <Amount>
            <sum>100</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner2</Id>
        </owner>
        <Amount>
            <sum>100</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner3</Id>
        </owner>
        <Amount>
            <sum>100</sum>
        </Amount>
    </userFee>
</feeList>
<user>
    <value>userB</value>
</user>    
<feeList>
    <userFee>
        <owner>
            <Id>owner1</Id>
        </owner>
        <Amount>
            <sum>120</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner2</Id>
        </owner>
        <Amount>
            <sum>100</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner3</Id>
        </owner>
        <Amount>
            <sum>180</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner3</Id>
        </owner>
        <Amount>
            <sum>100</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner4</Id>
        </owner>
        <Amount>
            <sum>75</sum>
        </Amount>
    </userFee>
    <userFee>
        <owner>
            <Id>owner4</Id>
        </owner>
        <Amount>
            <sum>25</sum>
        </Amount>
    </userFee>
</feeList>

There are 4 userFee elements in the feeList. Two of them belong to the same owner "owner1" and these needs to be merged as 1 and 2 of them belog to different owners. I need the following output:

user: userA    
Total sum: 400
count: 3 (basically no. of unique owner id's for the user fee
    owner and amount: owner1, 200 (Note there are 2 owner1 elements, and they need to be merged into one row with their sum)   
    owner and amount: owner2, 100
    owner and amount: owner3, 100

So far I have the following XSLT:
(...I am still struggling with group and merging them.)

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

  <xsl:template match="/">
    <xsl:for-each select="Fees">
   user: <xsl:value-of select="user/value"/>
      <!-- how to get count of unique userFee by owner ID -->
   Count:<xsl:value-of select="count(feeList/userFee)"/>
   Total Sum:<xsl:value-of select="sum(feeList/userFee/amount/sum)"/>
      <xsl:for-each select="feeList/userFee">
         <!-- how to group same owner into one and sum there amount -->
     owner and amount: <xsl:value-of select="owner/Id"/>, <xsl:value-of select="amount/sum"/>
      </xsl:for-each>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Unfortunately Limited to XSLT 1.0. Thank you

Upvotes: 2

Views: 130

Answers (2)

Daniel Haley
Daniel Haley

Reputation: 52888

I mean, i want the count to be 3. Count of 1 per each owner. Also, I am running into issue when i have more than one <feeList>. In the example i have only gave one but actually there can be many. <feeList> is one per user and i have multiple user in the actual file. I think the issue is with the <xsl:key name="overall" match="userFee" use="owner/Id" /> looking in the whole file. Can i make this key just look into each <feeList> element?

Based on this comment on the other answer, it sounds to me like you need to use a composite key using the both the owner/ID and also the user/value.

It would be much easier to tell for certain if you updated your question with an example that contains more than one user and corresponding feeList.

Also, to get a count of the number of owners per user you can create a variable that outputs a string character for each owner. The length of the string will be the number of owners. There's probably a much better way to do this, but it's not coming to mind right now.

Full example...

XML Input (updated to have more than one user and feeList)

<Fees>
    <user>
        <value>userA</value>
    </user>
    <feeList>
        <userFee>
            <owner>
                <Id>owner1</Id>
            </owner>
            <Amount>
                <sum>100</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner1</Id>
            </owner>
            <Amount>
                <sum>100</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner2</Id>
            </owner>
            <Amount>
                <sum>100</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner3</Id>
            </owner>
            <Amount>
                <sum>100</sum>
            </Amount>
        </userFee>
    </feeList>
    <user>
        <value>userB</value>
    </user>    
    <feeList>
        <userFee>
            <owner>
                <Id>owner1</Id>
            </owner>
            <Amount>
                <sum>120</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner2</Id>
            </owner>
            <Amount>
                <sum>100</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner3</Id>
            </owner>
            <Amount>
                <sum>180</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner3</Id>
            </owner>
            <Amount>
                <sum>100</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner4</Id>
            </owner>
            <Amount>
                <sum>75</sum>
            </Amount>
        </userFee>
        <userFee>
            <owner>
                <Id>owner4</Id>
            </owner>
            <Amount>
                <sum>25</sum>
            </Amount>
        </userFee>
    </feeList>
</Fees>

XSLT 1.0

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>
  <xsl:strip-space elements="*"/>

  <xsl:key name="user_fees" match="userFee" use="concat(../preceding-sibling::user[1]/value,'~',owner/Id)"/>

  <xsl:template match="/*">
    <xsl:for-each select="feeList">
      <xsl:variable name="totalOwners">
        <xsl:for-each select="userFee[count(.|key('user_fees',concat(../preceding-sibling::user[1]/value,'~',owner/Id))[1])=1]">
          <xsl:text>#</xsl:text>
        </xsl:for-each>
      </xsl:variable>
      <xsl:if test="position() > 1"><xsl:text>&#xA;</xsl:text></xsl:if>
      <xsl:value-of select="concat('User: ',preceding-sibling::user[1]/value,'&#xA;')"/>
      <xsl:value-of select="concat('Total Sum: ',sum(userFee/Amount/sum),'&#xA;')"/>
      <xsl:value-of select="concat('Count: ', string-length($totalOwners), '&#xA;')"/>
      <xsl:for-each 
        select="userFee[count(.|key('user_fees',concat(../preceding-sibling::user[1]/value,'~',owner/Id))[1])=1]">
        <xsl:value-of 
          select="concat('&#x9;owner and amount: ',
          owner/Id,
          ', ',
          sum(key('user_fees',concat(../preceding-sibling::user[1]/value,'~',owner/Id))/Amount/sum),'&#xA;')"/>
      </xsl:for-each>
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>

Output

User: userA
Total Sum: 400
Count: 3
    owner and amount: owner1, 200
    owner and amount: owner2, 100
    owner and amount: owner3, 100

User: userB
Total Sum: 600
Count: 4
    owner and amount: owner1, 120
    owner and amount: owner2, 100
    owner and amount: owner3, 280
    owner and amount: owner4, 100

Fiddle: http://xsltfiddle.liberty-development.net/nc4NzQ8

Upvotes: 1

zx485
zx485

Reputation: 29052

This is, as Tomalak mentioned, a task for Muenchian Grouping. Under this tag you will find a lot of examples of how to use it. In the beginning it can be a bit tricky to use and because of that XSLT-2.0 introduced the much simpler xsl:for-each-group for this kind of task.

However, here is one solution applying this method:

<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes" omit-xml-declaration="yes" />
  <xsl:key name="overall" match="userFee" use="owner/Id" />   <!-- key required for Muenchian method -->

  <xsl:template match="/Fees">
    User: <xsl:value-of select="user/value"/>
    <!-- iterate over unique owners - Muenchian method -->
    <xsl:for-each select="feeList/userFee[generate-id() = generate-id(key('overall',owner/Id)[1])]">
      Owner:<xsl:value-of select="owner/Id"/>
      <!-- how to get count of unique userFee by owner ID -->
      Count:<xsl:value-of select="count(key('overall',owner/Id))"/>
      <!-- how to sum the amount of one owner -->
      Total Sum:<xsl:value-of select="sum(key('overall',owner/Id)/Amount/sum)"/>
      <xsl:text>&#xa;</xsl:text>
    </xsl:for-each>
    Total count: <xsl:value-of select="count(feeList/userFee)" />
  </xsl:template>

</xsl:stylesheet>

Output is:

User: userA
Owner:owner1
Count:2
Total Sum:200

Owner:owner2
Count:1
Total Sum:100

Owner:owner3
Count:1
Total Sum:100

Total count: 4

Upvotes: 1

Related Questions