I did some research on how we can perform Group by Clause or Joins in
biztalk 2004 or 2006.
in my scenario, i want to integrate three different SQL Databases with
following scenario.

1- Get the Table A from System A
2- Get the Table B from System B

get both of them in BizTalk, Join these two tables and perform Group by on
three columns along with Sum on Amount column.

in this case i am using SQL Server databases but it can happen with other
system also. Core requirement is to get two different system messages, join
them and perform a group by on multiple columns. we are not suppose to use
Stored procedure here as we want to remove existing maintenance of stored
procedure and want to convert all integration to BizTalk solution. I can do
all this using my .NET code but is there a way to do this within mapper or
some where else in BizTalk.
BizTalk is a good messaging solution but i was wondering if BizTalk supports
above requirements???

Regards,

BizWorld.

[ Post a follow-up to this message ]

You should be able to do this using map with a custom Xslt.
Here is a quick sample of how to do the group by:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
version="1.0" xmlns:ns0="http://BizTalk.Enthusiast.LogicalSplitter.Orders"
xmlns:ns1="http://BizTalk.Enthusiast.LogicalSplitter.Order">
<xsl:output omit-xml-declaration="yes" version="1.0" method="xml" />
<xsl:key name="linekey" match="/ns1:Order/Line"
use="concat(@RequestedDate,@Qty)" />
<xsl:template match="/">
<xsl:apply-templates select="/ns1:Order" />
</xsl:template>
<xsl:template match="/ns1:Order">
<ns0:Orders>
<xsl:for-each select="Line">
<xsl:variable name="group" select="key('linekey',
concat(@RequestedDate,@Qty))" />
<xsl:if test="generate-id($group[1]) = generate-id()">
<ns1:Order>
<Header>
<xsl:attribute name="OrderNo">
<xsl:value-of select="ancestor::*[1]/Header/@OrderNo" />
</xsl:attribute>
<xsl:attribute name="OrderDate">
<xsl:value-of select="ancestor::*[1]/Header/@OrderDate" />
</xsl:attribute>
</Header>
<xsl:for-each select="$group">
<Line>
<xsl:attribute name="Item">
<xsl:value-of select="@Item" />
</xsl:attribute>
<xsl:attribute name="RequestedDate">
<xsl:value-of select="@RequestedDate" />
</xsl:attribute>
<xsl:attribute name="Qty">
<xsl:value-of select="@Qty" />
</xsl:attribute>
<xsl:attribute name="ExtendedPrice">
<xsl:value-of select="@ExtendedPrice" />
</xsl:attribute>
</Line>
</xsl:for-each>
<Footer NoLines="{count($group) }" TotalQty="{sum($group/@Qty)}"
TotalAmount="{format-number(sum($group/@ExtendedPrice), '####.00')}" />
</ns1:Order>
</xsl:if>
</xsl:for-each>
</ns0:Orders>
</xsl:template>
</xsl:stylesheet>



"BizWorld" <moonis.tahir@gmail.com> wrote in message
news:OyqtpvJOGHA.2472@TK2MSFTNGP11.phx.gbl...
>I did some research on how we can perform Group by Clause or Joins in
>biztalk 2004 or 2006.
> in my scenario, i want to integrate three different SQL Databases with
> following scenario.
>
> 1- Get the Table A from System A
> 2- Get the Table B from System B
>
> get both of them in BizTalk, Join these two tables and perform Group by on
> three columns along with Sum on Amount column.
>
> in this case i am using SQL Server databases but it can happen with other
> system also. Core requirement is to get two different system messages,
> join them and perform a group by on multiple columns. we are not suppose
> to use Stored procedure here as we want to remove existing maintenance of
> stored procedure and want to convert all integration to BizTalk solution.
> I can do all this using my .NET code but is there a way to do this within
> mapper or some where else in BizTalk.
> BizTalk is a good messaging solution but i was wondering if BizTalk
> supports above requirements???
>
> Regards,
>
> BizWorld.
>

[ Post a follow-up to this message ]