[XSL-LIST Mailing List Archive Home] [By Thread] [By Date]

Re: [xsl] select the group of rows based on the three XML fields and with these conditions.

---

---

At 2010-10-21 16:22 -0400, Peacock Lover wrote:

I would like to group based on fields of row element (combination of
StackNumber,BlockNumber and LocationCode) and select only higher
BookVersion (BookVersion) based rows in particular group(combination
of StackNumber,BlockNumber and LocationCode) and also to select the
rows of DeliveryMode with the value as TRANSIT.

Here is the input document
...
XSL (using saxon 9 parser) that I wrote:

<xsl:stylesheet version="1.0"

If you are using Saxon 9 then you don't have to use XSLT 1.0 ... you can use XSLT 2.0 as I've done below.

...
id(key('transitGroup',concat(StackNumber,BlockNumber,LocationCode))[1])]">

Using a simple concatenation as you have above will introduce ambiguities if the digits are similar ... for example:

concat( "123","456","789" ) is the same as concat( "12","34567","89" )

... so in my solution I introduced a delimiter between the fields that is unlikely to be found in the input XML because of end-of-line sequence normalization.

example group must be printed like this .

<row>
<StackNumber>20</StackNumber>
<BlockNumber>61001</BlockNumber>
<LocationCode>MON</LocationCode>
<StoreNumber>1013</StoreNumber>
<BookVersion>02</BookVersion>
</row>

In my result below I've preserved the order of the elements rather than put <BookVersion> at the end, but you can change that if you need to.

Also, I'm only outputing the *first* of the rows with the highest book version ... if you wanted *all* the rows with the highest book version then remove the "[1]" predicate.

I hope this helps. It is using many features of XSLT 2.0 not available in XSLT 1.0 that I suspect you will find useful in other solutions. You'll see the solution below is more declarative than the imperative style in your original work.

. . . . . . . . . . . Ken

T:\ftemp>type peacock.xml
<?xml version="1.0"?>
<Output>
    <row>
        <StackNumber>19</StackNumber>
        <BlockNumber>61001</BlockNumber>
        <DeliveryMode>TRANSIT</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>03</BookVersion>
        <StoreNumber>1010</StoreNumber>

    </row>
    <row>
        <StackNumber>20</StackNumber>
        <BlockNumber>61001</BlockNumber>
        <DeliveryMode>TRANSIT</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>01</BookVersion>
        <StoreNumber>1011</StoreNumber>
    </row>
    <row>
        <StackNumber>20</StackNumber>
        <BlockNumber>61001</BlockNumber>
        <DeliveryMode>TRANSIT</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>02</BookVersion>
        <StoreNumber>1013</StoreNumber>
    </row>
    <row>
        <StackNumber>21</StackNumber>
        <BlockNumber>61001</BlockNumber>
        <DeliveryMode>RECVD</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>03</BookVersion>
        <StoreNumber>1022</StoreNumber>
    </row>
    <row>
        <StackNumber>21</StackNumber>
        <BlockNumber>61001</BlockNumber>
        <DeliveryMode>TRANSIT</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>03</BookVersion>
        <StoreNumber>1022</StoreNumber>
    </row>
    <row>
        <StackNumber>22</StackNumber>
        <BlockNumber>15098</BlockNumber>
        <DeliveryMode>TRANSIT</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>01</BookVersion>
        <StoreNumber>1010</StoreNumber>
    </row>
    <row>
        <StackNumber>22</StackNumber>
        <BlockNumber>22456</BlockNumber>
        <DeliveryMode>TRANSIT</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>02</BookVersion>
        <StoreNumber>1011</StoreNumber>
    </row>
    <row>
        <StackNumber>22</StackNumber>
        <BlockNumber>22456</BlockNumber>
        <DeliveryMode>TRANSIT</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>03</BookVersion>
        <StoreNumber>1012</StoreNumber>
    </row>
    <row>
        <StackNumber>22</StackNumber>
        <BlockNumber>22456</BlockNumber>
        <DeliveryMode>RECVD</DeliveryMode>
        <LocationCode>MON</LocationCode>
        <BookVersion>02</BookVersion>
        <StoreNumber>1021</StoreNumber>
    </row>
</Output>

T:\ftemp>call xslt2 peacock.xml peacock.xsl
<?xml version="1.0" encoding="UTF-8"?>
<Output>
   <row>
      <StackNumber>19</StackNumber>
      <BlockNumber>61001</BlockNumber>
      <LocationCode>MON</LocationCode>
      <BookVersion>03</BookVersion>
      <StoreNumber>1010</StoreNumber>
   </row>
   <row>
      <StackNumber>20</StackNumber>
      <BlockNumber>61001</BlockNumber>
      <LocationCode>MON</LocationCode>
      <BookVersion>02</BookVersion>
      <StoreNumber>1013</StoreNumber>
   </row>
   <row>
      <StackNumber>21</StackNumber>
      <BlockNumber>61001</BlockNumber>
      <LocationCode>MON</LocationCode>
      <BookVersion>03</BookVersion>
      <StoreNumber>1022</StoreNumber>
   </row>
   <row>
      <StackNumber>22</StackNumber>
      <BlockNumber>15098</BlockNumber>
      <LocationCode>MON</LocationCode>
      <BookVersion>01</BookVersion>
      <StoreNumber>1010</StoreNumber>
   </row>
   <row>
      <StackNumber>22</StackNumber>
      <BlockNumber>22456</BlockNumber>
      <LocationCode>MON</LocationCode>
      <BookVersion>03</BookVersion>
      <StoreNumber>1012</StoreNumber>
   </row>
</Output>
T:\ftemp>type peacock.xsl
<?xml version="1.0" encoding="US-ASCII"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                exclude-result-prefixes="xsd"
                version="2.0">

<xsl:output indent="yes"/>

<xsl:template match="/">
  <Output>
    <xsl:for-each-group select="*/row[DeliveryMode='TRANSIT']"
                        group-by="concat(StackNumber,'&#xd;',
                                         BlockNumber,'&#xd;',LocationCode)">
      <xsl:for-each select="current-group()
                            [xsd:decimal(BookVersion)=
                            max(current-group()/BookVersion/xsd:decimal(.))]
                            [1]">
        <row>
          <xsl:copy-of select="* except DeliveryMode"/>
        </row>
      </xsl:for-each>
    </xsl:for-each-group>
  </Output>
</xsl:template>

</xsl:stylesheet>
T:\ftemp>rem Done!

--
XSLT/XQuery training:   after http://XMLPrague.cz 2011-03-28/04-01
Vote for your XML training:   http://www.CraneSoftwrights.com/s/i/
Crane Softwrights Ltd.          http://www.CraneSoftwrights.com/s/
G. Ken Holman                 mailto:gkholman@xxxxxxxxxxxxxxxxxxxx
Male Cancer Awareness Nov'07  http://www.CraneSoftwrights.com/s/bc
Legal business disclaimers:  http://www.CraneSoftwrights.com/legal

---