Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XSLT
Reload this Page "group by" in XSLT 1.0 (please help)
|
XSLT General questions and answers about XSLT. For issues strictly specific to the book XSLT 1.1 Programmers Reference, please post to that forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XSLT section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 11th, 2009, 02:58 AM
Authorized User
  
Join Date: Sep 2008
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default "group by" in XSLT 1.0 (please help)
Hi specialists!

I need to do grouping

I need to make kind of a "group by" node SAP_LINE_ID by three fields(ORDER_ID, ORDER_LINE, SAP_DOC_ID), I mean to group together nodes where ORDER_ID equal to each other, ORDER_ID equal to each other and ORDER_LINE SAP_DOC_ID equal to each other:

Source:
Code:
<?xml version="1.0" encoding="utf-8"?>
<ActualAmountsMT>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>1</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1001</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>2</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1002</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>2</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1022</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>3</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1003</SAP_LINE_ID>
	</row>
	
</ActualAmountsMT>

Target:

Code:
<ActualAmountsOutMT>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>1</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1001</SAP_LINE_ID>
		<SapSalesDocumentLines>
			<SapSalesDocumentLine>1001</SapSalesDocumentLine>
		</SapSalesDocumentLines>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>2</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SapSalesDocumentLines>
			<SapSalesDocumentLine>1002</SapSalesDocumentLine>
			<SapSalesDocumentLine>1022</SapSalesDocumentLine>
		</SapSalesDocumentLines>
	</row>

	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>3</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SapSalesDocumentLines>
			<SapSalesDocumentLine>1003</SapSalesDocumentLine>
		</SapSalesDocumentLines>
	</row>
</ActualAmountsOutMT>

Well, actually I have no idea how to do this, I suppose It must be something like that:

Code:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

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

	<xsl:template match="ActualAmountsPazaMT">
		<xsl:copy>
			<xsl:apply-templates select="row"/>
		</xsl:copy>
	</xsl:template>

	<xsl:template match="row">
		<xsl:copy>
			<xsl:apply-templates select="ORDER_ID | ORDER_LINE | SAP_DOC_ID"/>
		</xsl:copy>
	</xsl:template>

	<xsl:template match="SapSalesDocumentLines">
		<xsl:copy>
			<xsl:apply-templates select="/ActualAmountsPazaMT/row/SapSalesDocumentLines[ORDER_ID = current()/ORDER_ID and ORDER_LINE = current()/ORDER_LINE and SAP_DOC_ID = current()/SAP_DOC_ID]"/>
			<SapSalesDocumentLine>
				<xsl:apply-templates select="SAP_LINE_ID"/>
			</SapSalesDocumentLine>
		</xsl:copy>
	</xsl:template>

	<xsl:template match="*">
		<xsl:copy>
			<xsl:apply-templates/>
		</xsl:copy>
	</xsl:template>

</xsl:stylesheet>

Thanks for the help.
Last edited by dani1; March 11th, 2009 at 10:52 AM..
 
Old March 11th, 2009, 04:08 AM
Friend of Wrox
  
Join Date: Jun 2008
Posts: 291
Thanks: 9
Thanked 29 Times in 29 Posts
Thumbs up
Try this:

Code:
 
<xsl:template match="/">
<xsl:apply-templates select="ActualAmountsMT"/>
</xsl:template>

<xsl:template match="ActualAmountsMT">
<ActualAmountsOutMT>
<xsl:apply-templates select="row"/>
</ActualAmountsOutMT>
</xsl:template>

<xsl:template match="row[ORDER_LINE = following::ORDER_LINE]">
<row>
<xsl:choose>
<xsl:when test="ORDER_LINE = following::ORDER_LINE">
<xsl:copy-of select="ORDER_ID"></xsl:copy-of>
<xsl:copy-of select="ORDER_LINE"></xsl:copy-of>
<xsl:copy-of select="SAP_DOC_ID"></xsl:copy-of>
<SapSalesDocumentLines>
<SapSalesDocumentLine>
<xsl:value-of select="SAP_LINE_ID"/>
</SapSalesDocumentLine>
<SapSalesDocumentLine>
<xsl:value-of select="following::SAP_LINE_ID"/>
</SapSalesDocumentLine>
</SapSalesDocumentLines>
</xsl:when>
<xsl:otherwise></xsl:otherwise>
</xsl:choose>
</row>
</xsl:template>

<xsl:template match="row[ORDER_LINE = preceding::ORDER_LINE]"/>

<xsl:template match="row">
<row>
<xsl:copy-of select="ORDER_ID"/>
<xsl:copy-of select="ORDER_LINE"/>
<xsl:copy-of select="SAP_DOC_ID"/>
<SapSalesDocumentLines>
<SapSalesDocumentLine>
<xsl:value-of select="SAP_LINE_ID"/>
</SapSalesDocumentLine>
</SapSalesDocumentLines>
</row>
</xsl:template>


There may be better and easy solution than this.
__________________
Rummy
 
Old March 11th, 2009, 04:09 AM
joefawcett's Avatar
Wrox Author
  
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default
The first post in this forum has a link to grouping examples. The technique is called Muenchian grouping. Have a look at these, you shoukld probably construct an xsl:key that retrieves the ORDER_LINE elements.
__________________
Joe
http://joe.fawcett.name/
 
Old March 11th, 2009, 10:48 AM
Authorized User
  
Join Date: Sep 2008
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default
Quote:
Originally Posted by mrame View Post
Try this:
Thanks for help, but it works only partly :(

For example in this source:


Code:
<ActualAmountsMT>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>1</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1001</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>2</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1002</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>2</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1022</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>3</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SAP_LINE_ID>1003</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>319</ORDER_ID>
		<ORDER_LINE>1</ORDER_LINE>
		<SAP_DOC_ID>1900319</SAP_DOC_ID>
		<SAP_LINE_ID>111</SAP_LINE_ID>
	</row>
	<row>
		<ORDER_ID>319</ORDER_ID>
		<ORDER_LINE>2</ORDER_LINE>
		<SAP_DOC_ID>1900319</SAP_DOC_ID>
		<SAP_LINE_ID>222</SAP_LINE_ID>
	</row>
</ActualAmountsMT>
I've got this output:

Code:
<ActualAmountsOutMT>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>1</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SapSalesDocumentLines>
			<SapSalesDocumentLine>1001</SapSalesDocumentLine>
			<SapSalesDocumentLine>1002</SapSalesDocumentLine>
		</SapSalesDocumentLines>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>2</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SapSalesDocumentLines>
			<SapSalesDocumentLine>1002</SapSalesDocumentLine>
			<SapSalesDocumentLine>1022</SapSalesDocumentLine>
		</SapSalesDocumentLines>
	</row>
	<row>
		<ORDER_ID>40</ORDER_ID>
		<ORDER_LINE>3</ORDER_LINE>
		<SAP_DOC_ID>10040</SAP_DOC_ID>
		<SapSalesDocumentLines>
			<SapSalesDocumentLine>1003</SapSalesDocumentLine>
		</SapSalesDocumentLines>
	</row>
</ActualAmountsOutMT>

Also, I forgot some very important thing(I fixed it i my first post) I need to do grouping by all three fields(ORDER_ID, ORDER_LINE, SAP_DOC_ID), I mean to group together nodes where ORDER_ID equal to each other ORDER_ID equal to each other and ORDER_LINE SAP_DOC_ID equal to each other.
 
Old March 11th, 2009, 10:56 AM
Authorized User
  
Join Date: Sep 2008
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default
Quote:
Originally Posted by joefawcett View Post
The first post in this forum has a link to grouping examples. The technique is called Muenchian grouping. Have a look at these, you shoukld probably construct an xsl:key that retrieves the ORDER_LINE elements.
Thanks, joefawcett!
 
Old March 11th, 2009, 11:04 AM
mhkay's Avatar
Wrox Author
  
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default
For grouping problems, move to XSLT 2.0 if at all possible.

Failing that, learn about Muenchian grouping. It is a bit more complicated to understand, but far more efficient and flexible than the "x = following::x" technique advocated by Rummy.
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
 
Old March 11th, 2009, 11:18 AM
Authorized User
  
Join Date: Sep 2008
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default
Quote:
Originally Posted by mhkay View Post
For grouping problems, move to XSLT 2.0 if at all possible.

Failing that, learn about Muenchian grouping. It is a bit more complicated to understand, but far more efficient and flexible than the "x = following::x" technique advocated by Rummy.
Hi, mhkay!

Unfortunately I need to use XSLT 1.0 :( working with SAP XI.
I am trying to use Muenchian grouping, but without any success :( yet
Last edited by dani1; March 11th, 2009 at 11:46 AM..
 
Old March 11th, 2009, 11:37 AM
Friend of Wrox
  
Join Date: Nov 2007
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default
Here is an XSLT 1.0 example using Muenchian grouping on your data posted in the first post in this thread:
Code:
<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:output method="xml" indent="yes"/>
  <xsl:strip-space elements="*"/>
  
  <xsl:key name="group1"
           match="row"
           use="concat(ORDER_ID, '|', ORDER_LINE, '|', SAP_DOC_ID)"/>
  
           
  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>
  
  <xsl:template match="ActualAmountsMT">
    <ActualAmountsOutMT>
      <xsl:apply-templates select="row[generate-id() = generate-id(key('group1', concat(ORDER_ID, '|', ORDER_LINE, '|', SAP_DOC_ID))[1])]"/>
    </ActualAmountsOutMT>
  </xsl:template>
  
  <xsl:template match="row">
    <xsl:copy>
      <xsl:apply-templates select="*[not(self::SAP_LINE_ID)]"/>
      <SapSalesDocumentLines>
        <xsl:apply-templates select="key('group1', concat(ORDER_ID, '|', ORDER_LINE, '|', SAP_DOC_ID))/SAP_LINE_ID"/>
      </SapSalesDocumentLines>
    </xsl:copy>
  </xsl:template>
  
  <xsl:template match="SAP_LINE_ID">
    <SapSalesDocumentLine>
      <xsl:apply-templates/>
    </SapSalesDocumentLine>
  </xsl:template>

</xsl:stylesheet>
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
The Following User Says Thank You to Martin Honnen For This Useful Post:
dani1 (March 12th, 2009)
 
Old March 12th, 2009, 03:21 AM
Authorized User
  
Join Date: Sep 2008
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default
Quote:
Originally Posted by Martin Honnen View Post
Here is an XSLT 1.0 example using Muenchian grouping on your data posted in the first post in this thread:
Thank you Martin, that's it.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Code not going as planned: "icicle" vs "savedinstancestate" joopthecat BOOK: Professional Android Application Development ISBN: 978-0-470-34471-2 3 May 3rd, 2009 03:09 PM
The Myth of "Good Web Design is in the Graphics" iminore PHP Databases 0 March 9th, 2009 02:02 PM
how to read xml files doing select="document()" ? eruditionist XSLT 2 February 10th, 2009 05:08 PM
Message received by "FILE" adapter is suspended skindika Biztalk 0 February 2nd, 2009 07:06 PM
google Response.Write(Request.QueryString("q")) senol01 ASP.NET 2.0 Basics 1 January 2nd, 2009 12:01 PM




Contact Us - Wrox - Privacy Statement - Top

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.