- This topic has 14 replies, 1 voice, and was last updated 8 years, 3 months ago by
.
-
Posts
-
-
Hi All,
I am working on integrating one of our legacy system to SAP. To make the long story short, i am receving a bunch of General Ledger as a single message meaning it looks like this
<Arrary of GL's>
<GL>
<no>1</no>
<amount>-200</amount>
</GL>
<GL>
<no>1</no>
<amount>-300</amount>
</GL>
<no>2</no>
<amount>500</amount>
</Array of GL's>
What i am expecting whenever i see a same GL no in the message i need to add the currency amount and spit as a single GL like above i have GL no 1 as two entries so it should like
<Arrary of GL's>
<GL>
<no>1</no>
<amount>-500</amount>
</GL>
<no>2</no>
<amount>500</amount>
</Array of GL's>
so now the question is how to do that? in maps? if yes how?
-
Mani,
Look at using the cumulative functoids to consolidate the data. I know this is a 2004 link but the functoids haven't changed too much. Below is a sample.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sdk/htm/ebiz_prog_map_agwu.asp
-
Mani,
Look at using the cumulative functoids to
consolidate the data. I know this is a 2004 link but the functoids
haven't changed too much. Below is a sample.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sdk/htm/ebiz_prog_map_agwu.asp
-
You will need one cumulative functoid for each GL code, and I would imagine this could get messy.
Another way would be to use a custom Xslt and use Muenchian grouping (uses xsl:key) to group and sum the amounts
e.g.
<?xml version="1.0" encoding="utf-16"?>
<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">
<xsl:output indent="yes" omit-xml-declaration="yes" version="1.0" method="xml" />
<xsl:key name="glkey" match="/Root/GL" use="no"/>
<xsl:template match="/">
<xsl:apply-templates select="/Root" />
</xsl:template>
<xsl:template match="/Root">
<Root>
<xsl:for-each select="GL">
<xsl:variable name="group" select="key('glkey', no)"/>
<xsl:if test="generate-id($group[1]) = generate-id()">
<GL>
<no><xsl:value-of select="no" /></no>
<amount><xsl:value-of select="sum($group/amount)"/></amount>
</GL>
</xsl:if>
</xsl:for-each>
</Root>
</xsl:template>
</xsl:stylesheet>will convert this:
<Root>
<GL>
<no>1</no>
<amount>-200</amount>
</GL>
<GL>
<no>1</no>
<amount>-300</amount>
</GL>
<GL>
<no>2</no>
<amount>500</amount>
</GL>
</Root>to this:
<Root>
<GL>
<no>1</no>
<amount>-500</amount>
</GL>
<GL>
<no>2</no>
<amount>500</amount>
</GL>
</Root>-
-
Hi Greg,
this sample helped me but there is a small change in structure of the message and i haven't done much in XSLT.
the message looks like
<root>
<GL>
<amount>200</amount>
<glaccount>
<number>1</number>
</glaccount>
</GL>
</root>
now what is the change in my XSLT? i know the group you have specified in the xslt has to change. let me know what the change is?
-
You need to change the xpath for the use reference to "glaccount/number", and also the output node structure:
<?xml version="1.0" encoding="utf-16"?>
<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">
<xsl:output indent="yes" omit-xml-declaration="yes" version="1.0" method="xml" />
<xsl:key name="glkey" match="/Root/GL" use="glaccount/number"/>
<xsl:template match="/">
<xsl:apply-templates select="/Root" />
</xsl:template>
<xsl:template match="/Root">
<Root>
<xsl:for-each select="GL">
<xsl:variable name="group" select="key('glkey', glaccount/number)"/>
<xsl:if test="generate-id($group[1]) = generate-id()">
<GL>
<amount><xsl:value-of select="sum($group/amount)"/></amount>
<glaccount>
<number><xsl:value-of select="glaccount/number" /></number>
</glaccount>
</GL>
</xsl:if>
</xsl:for-each>
</Root>
</xsl:template>
</xsl:stylesheet>-
hi greg,
i am using the following xslt but i get some errors see below for errors;
<?xml version="1.0" encoding="utf-16"?>
<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">
<xsl:output indent="yes" omit-xml-declaration="yes" version="1.0" method="xml"/>
<xsl:key name="glkey" match="/GLS/GL" use="number"/>
<xsl:template match="/">
<xsl:apply-templates select="/*[local-name()='GLS' and namespace-uri()='http://TestLooping.first'%5D"/>
</xsl:template>
<xsl:template match="/*[local-name()='GLS' and namespace-uri()='http://TestLooping.first'%5D">
<GLS>
<xsl:for-each select="/*[local-name()='GLS' and namespace-uri()='http://TestLooping.first'%5D/*%5Blocal-name()='GL' and namespace-uri()='']">
<xsl:variable name="group" select="key('glkey',number)"/>
<xsl:if test="generate-id($group[1])=generate-id()">
<GL>
<xsl:element name="number"><xsl:value-of select="number"/></xsl:element>
<xsl:element name="amount"><xsl:value-of select="sum($group/amount/text())"/></xsl:element>
</GL>
</xsl:if>
</xsl:for-each>
</GLS>
</xsl:template>
</xsl:stylesheet>Error 2 The Script functoid, that connects to target node 'amount', has an error in its 'Xslt Call-Template' definition: 'Unexpected XML declaration. The XML declaration must be the first node in the document, and no white space characters are allowed to appear before it. Line 1, position 81.' D:\TestProjects\TestLooping\TestLooping\First_XML_To_Destination_XML.btm
Error 5 XSL transform error: Unable to write output instance to the following <file:///D:\TestProjects\TestLooping\output.xml>. XSLT compile error at (10,28). See InnerException for details. '' is an invalid QName. D:\TestProjects\TestLooping\TestLooping\First_XML_To_Destination_XML.btm
Thanks
-Mani
-
Mani,
You cannot use this xslt from within a scripting functoid. You need to use the custom Xslt feature of the mapper.
Cut the Xslt above, and paste into a file, save this file (as Unicode) into your project directory, call it <mapname>.xsl
In your project, Add Existing Item, and select this file (this is not strictly necessary but helps with source control)
Open your map and select the grid. In the Properties window there should be a Custom Xslt property. Use the ellipsis and select the xsl file created above.
This overrides the creation of the Xslt by the Biztalk Mapper and uses the Xslt provided in the file, so all the links in the map will no longer be used. You will need to replicate these in the Xslt file.
-
Hi Greg,
I did the same what you said,
i get the error says Output validation error: "Root element is missing". Any idea y this is happening
<?xml version="1.0" encoding="utf-16" ?>– <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"><xsl:output indent="yes" omit-xml-declaration="yes" version="1.0" method="xml" /><xsl:key name="glkey" match="/GLS/GL" use="number" /><xsl:apply-templates select="/GLS" /></xsl:template></GLS></xsl:template></xsl:stylesheet>And my input instance file looks like this<ns0:GLS xmlns:ns0="http://TestLooping.first">
<GL>
<amount>10.4</amount>
<number>10</number>
</GL>
<GL>
<amount>10.4</amount>
<number>10</number>
</GL>
</ns0:GLS> -
Hi Greg,
I did the same what you said,
i get the error says Output validation error: "Root element is missing". Any idea y this is happening
<?xml version="1.0" encoding="utf-16" ?>– <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"><xsl:output indent="yes" omit-xml-declaration="yes" version="1.0" method="xml" /><xsl:key name="glkey" match="/GLS/GL" use="number" /><xsl:apply-templates select="/GLS" /></xsl:template></GLS></xsl:template></xsl:stylesheet>And my input instance file looks like this<ns0:GLS xmlns:ns0="http://TestLooping.first">
<GL>
<amount>10.4</amount>
<number>10</number>
</GL>
<GL>
<amount>10.4</amount>
<number>10</number>
</GL>
</ns0:GLS>-
You need to take into account the namespaces in the Xslt
<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:ns0="http://TestLooping.first" exclude-result-prefixes="msxsl" version="1.0">
<xsl:output indent="yes" omit-xml-declaration="yes" version="1.0" method="xml" />
<xsl:key name="glkey" match="/ns0:GLS/GL" use="number"/>
<xsl:template match="/">
<xsl:apply-templates select="/ns0:GLS" />
</xsl:template>
<xsl:template match="/ns0:GLS">
<ns0:GLS>
<xsl:for-each select="GL">
<xsl:variable name="group" select="key('glkey', number)"/>
<xsl:if test="generate-id($group[1]) = generate-id()">
<GL>
<amount>
<xsl:value-of select="sum($group/amount)"/>
</amount>
<number>
<xsl:value-of select="number" />
</number>
</GL>
</xsl:if>
</xsl:for-each>
</ns0:GLS>
</xsl:template>
</xsl:stylesheet>
-
-
Hi Greg,
Thanks i got that working my last post with error that was caused by the namespace problem. Now everything is fine and working well.
Thanks
Mani
-
-
-
-
-
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.