Warm tip: This article is reproduced from stackoverflow.com, please click
grouping xml xslt

xslt remove record based on criteria and groupBy

发布于 2020-04-03 23:39:34

I'm struggling with a difficult xsl transformation and I can't figure out how to make it work. As input I have a record collection. Each record is identified by two keys (pk1 and pk2) and it has a qualifier. The goal is copying those records grouped by pk1 and pk2. And this is simple. The problem comes when I have to copy only those where the occurrences of qualifier A and B are the same. Here is the input file.

<Data>
<!-- first case -->
<record>
    <pk1>0001</pk1>
    <pk2>AAA</pk2>
    <quantity>50.00</quantity>
    <qualifier>A</qualifier>
</record>
<!-- second case -->
<record>
    <pk1>0002</pk1>
    <pk2>AAA</pk2>
    <quantity>10.00</quantity>
    <qualifier>A</qualifier>
</record>
<record>
    <pk1>0002</pk1>
    <pk2>AAA</pk2>
    <quantity>10.00</quantity>
    <qualifier>B</qualifier>
</record>
<record>
    <pk1>0002</pk1>
    <pk2>BBB</pk2>
    <quantity>15.00</quantity>
    <qualifier>A</qualifier>
</record>
<!-- third case -->
<record>
    <pk1>0003</pk1>
    <pk2>AAA</pk2>
    <quantity>20.00</quantity>
    <qualifier>A</qualifier>
</record>
<record>
    <pk1>0003</pk1>
    <pk2>AAA</pk2>
    <quantity>20.00</quantity>
    <qualifier>B</qualifier>
</record>
<record>
    <pk1>0003</pk1>
    <pk2>AAA</pk2>
    <quantity>20.00</quantity>
    <qualifier>A</qualifier>
</record>
<record>
    <pk1>0003</pk1>
    <pk2>BBB</pk2>
    <quantity>70.00</quantity>
    <qualifier>A</qualifier>
</record>
<record>
    <pk1>0003</pk1>
    <pk2>BBB</pk2>
    <quantity>70.00</quantity>
    <qualifier>B</qualifier>
</record>
<!-- fourth case -->
<record>
    <pk1>0004</pk1>
    <pk2>AAA</pk2>
    <quantity>100.00</quantity>
    <qualifier>B</qualifier>
</record>

And this is the expected output.

<Data>
<!-- first case -->
<!-- second case -->
<record>
    <pk1>0002</pk1>
    <pk2>AAA</pk2>
    <quantity>10.00</quantity>
    <qualifier>A</qualifier>
</record>
<!-- third case -->
<record>
    <pk1>0003</pk1>
    <pk2>BBB</pk2>
    <quantity>70.00</quantity>
    <qualifier>A</qualifier>
</record>
<!-- fourth case -->

The explanation for each case is: First Case: 0001|AAA|A doesn't have its pair 0001|AAA|B -> remove

Second Case: 0002|AAA|A has its pair 0002|AAA|B -> keep the "A" one

Third Case: 0003|AAA|A has its pair 0003|AAA|B but there's one more 0003|AAA|A, which means for this group "A"s are not the same occurrences of "B"s -> remove

Third Case: 0003|BBB|A has its pair 0003|BBB|B -> keep the "A" one

Fourth case: 0004|AAA|A doesn't have its pair 0004|AAA|B -> remove

As I said, I don't know how to achieve and wheter it's doable with xslt. Could you please help me? Thank you.

Questioner
James Taylor
Viewed
52
michael.hor257k 2020-01-31 20:46

Consider the following simplified example:

XML

<Data>
    <!-- first case -->
    <record>
        <group>0001</group>
        <quantity>50.00</quantity>
        <qualifier>A</qualifier>
    </record>
    <!-- second case -->
    <record>
        <group>0002</group>
        <quantity>10.00</quantity>
        <qualifier>A</qualifier>
    </record>
    <record>
        <group>0002</group>
        <quantity>11.00</quantity>
        <qualifier>B</qualifier>
    </record>
    <record>
        <group>0002</group>
        <quantity>12.00</quantity>
        <qualifier>A</qualifier>
    </record>
    <record>
        <group>0002</group>
        <quantity>13.00</quantity>
        <qualifier>B</qualifier>
    </record>
    <!-- third case -->
    <record>
        <group>0003</group>
        <quantity>20.00</quantity>
        <qualifier>A</qualifier>
    </record>
    <record>
        <group>0003</group>
        <quantity>30.00</quantity>
        <qualifier>B</qualifier>
    </record>
    <record>
        <group>0003</group>
        <quantity>40.00</quantity>
        <qualifier>A</qualifier>
    </record>
</Data>

XSLT 2.0

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

<xsl:template match="/Data">
    <Data>
        <xsl:for-each-group select="record" group-by="group">
            <xsl:if test="count(current-group()[qualifier='A'])=count(current-group()[qualifier='B'])">
                <record>
                    <xsl:copy-of select="group" />
                    <quantity>
                        <xsl:value-of select="sum(current-group()/quantity)" />
                    </quantity>
                </record>
            </xsl:if>
        </xsl:for-each-group>
    </Data>
</xsl:template>

</xsl:stylesheet>

Result

<Data>
   <record>
      <group>0002</group>
      <quantity>46</quantity>
   </record>
</Data>

Demo: https://xsltfiddle.liberty-development.net/pPJ9hEe