IDOC to XML Mapping (Network Steve Forum)

IDOC to XML Mapping

Hi Sarva,

You can use Cumulative Sum functoid with second parameter as "1".

I have created one sample xsd from the sample u provided. Used below instance for testing:

<ns0:ZPAYMENT_EXTRACT xmlns:ns0="http://sample_dynamicXSLT.input_idocSchema">
<IDOC>
<BEGIN>BEGIN_0</BEGIN>
<EDI_DC40>
<field1>field1_0</field1>
<field2>field2_0</field2>
</EDI_DC40>
<Z1PAYHDR>
<SEGMENT>Girish</SEGMENT>
<ACCOUNT>111</ACCOUNT>
<XMIT_DATE>XMIT_DATE_0</XMIT_DATE>
<Z1PAYDTL>
<PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
<AMOUNT>100</AMOUNT>
<CREDIT_CARD>ABC</CREDIT_CARD>
</Z1PAYDTL>
<Z1PAYDTL>
<PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
<AMOUNT>200</AMOUNT>
<CREDIT_CARD>PQR</CREDIT_CARD>
</Z1PAYDTL>
<Z1PAYDTL>
<PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
<AMOUNT>300</AMOUNT>
<CREDIT_CARD>XYZ</CREDIT_CARD>
</Z1PAYDTL>
</Z1PAYHDR>
<Z1PAYHDR>
<SEGMENT>Naveen</SEGMENT>
<ACCOUNT>222</ACCOUNT>
<XMIT_DATE>XMIT_DATE_0</XMIT_DATE>
<Z1PAYDTL>
<PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
<AMOUNT>40</AMOUNT>
<CREDIT_CARD>ABC</CREDIT_CARD>
</Z1PAYDTL>
<Z1PAYDTL>
<PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
<AMOUNT>80</AMOUNT>
<CREDIT_CARD>PQR</CREDIT_CARD>
</Z1PAYDTL>
</Z1PAYHDR>
</IDOC>
</ns0:ZPAYMENT_EXTRACT>

Got the output as:

<ns0:ZPAYMENT_OUT xmlns:ns0="http://sample_dynamicXSLT.output_idocSchema">
<CUSTOMER>
<SEGMENT>Girish</SEGMENT>
<ACCOUNT>111</ACCOUNT>
<TOTALAMOUNT>600</TOTALAMOUNT>
</CUSTOMER>
<CUSTOMER>
<SEGMENT>Naveen</SEGMENT>
<ACCOUNT>222</ACCOUNT>
<TOTALAMOUNT>120</TOTALAMOUNT>
</CUSTOMER>
</ns0:ZPAYMENT_OUT>

February 11th, 2015 6:36am

You can use custom XSLT and the Muenchian Grouping for this.

In a simplified example where input is:

<Z1PAYHDR>
  <Z1PAYDTL>
    <AMOUNT>100</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>1000</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>40</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
</Z1PAYHDR>

And the XSLT is:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl xsl" version="1.0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:key name="DetailCC" match="Z1PAYDTL" use="CREDIT_CARD" />
  <xsl:template match="Z1PAYHDR">
    <Output>
      <xsl:for-each select="Z1PAYDTL[count(. | key('DetailCC', CREDIT_CARD)[1]) = 1]">
        <xsl:variable name="card" select="CREDIT_CARD" />
        <CC>
          <Card>
            <xsl:value-of select="$card" />
          </Card>
          <TotalAmount>
            <xsl:value-of select="sum(//Z1PAYDTL[CREDIT_CARD = $card]/AMOUNT)" />
          </TotalAmount>
        </CC>
      </xsl:for-each>
    </Output>
  </xsl:template>
</xsl:stylesheet>

You will get the folllowing output:

<Output>
  <CC>
    <Card>VISA</Card>
    <TotalAmount>1110</TotalAmount>
  </CC>
  <CC>
    <Card>MASTER</Card>
    <TotalAmount>50</TotalAmount>
  </CC>
</Output>

Morten la Cour

Marked as answer by SarvaSetty 20 hours 17 minutes ago

Free Windows Admin Tool Kit Click here and download it now

February 11th, 2015 2:25pm

You can use custom XSLT and the Muenchian Grouping for this.

In a simplified example where input is:

<Z1PAYHDR>
  <Z1PAYDTL>
    <AMOUNT>100</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>1000</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>40</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
</Z1PAYHDR>

And the XSLT is:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl xsl" version="1.0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:key name="DetailCC" match="Z1PAYDTL" use="CREDIT_CARD" />
  <xsl:template match="Z1PAYHDR">
    <Output>
      <xsl:for-each select="Z1PAYDTL[count(. | key('DetailCC', CREDIT_CARD)[1]) = 1]">
        <xsl:variable name="card" select="CREDIT_CARD" />
        <CC>
          <Card>
            <xsl:value-of select="$card" />
          </Card>
          <TotalAmount>
            <xsl:value-of select="sum(//Z1PAYDTL[CREDIT_CARD = $card]/AMOUNT)" />
          </TotalAmount>
        </CC>
      </xsl:for-each>
    </Output>
  </xsl:template>
</xsl:stylesheet>

You will get the folllowing output:

<Output>
  <CC>
    <Card>VISA</Card>
    <TotalAmount>1110</TotalAmount>
  </CC>
  <CC>
    <Card>MASTER</Card>
    <TotalAmount>50</TotalAmount>
  </CC>
</Output>

Morten la Cour

Marked as answer by SarvaSetty Wednesday, February 11, 2015 3:08 PM

February 11th, 2015 2:25pm

You can use custom XSLT and the Muenchian Grouping for this.

In a simplified example where input is:

<Z1PAYHDR>
  <Z1PAYDTL>
    <AMOUNT>100</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>1000</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>40</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
</Z1PAYHDR>

And the XSLT is:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl xsl" version="1.0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:key name="DetailCC" match="Z1PAYDTL" use="CREDIT_CARD" />
  <xsl:template match="Z1PAYHDR">
    <Output>
      <xsl:for-each select="Z1PAYDTL[count(. | key('DetailCC', CREDIT_CARD)[1]) = 1]">
        <xsl:variable name="card" select="CREDIT_CARD" />
        <CC>
          <Card>
            <xsl:value-of select="$card" />
          </Card>
          <TotalAmount>
            <xsl:value-of select="sum(//Z1PAYDTL[CREDIT_CARD = $card]/AMOUNT)" />
          </TotalAmount>
        </CC>
      </xsl:for-each>
    </Output>
  </xsl:template>
</xsl:stylesheet>

You will get the folllowing output:

<Output>
  <CC>
    <Card>VISA</Card>
    <TotalAmount>1110</TotalAmount>
  </CC>
  <CC>
    <Card>MASTER</Card>
    <TotalAmount>50</TotalAmount>
  </CC>
</Output>

Morten la Cour

Marked as answer by SarvaSetty Wednesday, February 11, 2015 3:08 PM

Free Windows Admin Tool Kit Click here and download it now

February 11th, 2015 2:25pm

Hi,

We have a requirement to sum up the total amount by credit card

i.e.. there will be multiple credit cards, and for each credit card there will be multiple entries,

so i need to sum up AMOUNT for each CREDITCARD and map it to TOTALAMOUNT in Destination.

Please tell me the best way to do this?

Thanks,

Sarva.

February 12th, 2015 1:58am

Hi Sarva,

Can you please provide us sample input and expected output?

Free Windows Admin Tool Kit Click here and download it now

February 12th, 2015 4:47am

You can use custom XSLT and the Muenchian Grouping for this.

In a simplified example where input is:

<Z1PAYHDR>
  <Z1PAYDTL>
    <AMOUNT>100</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>1000</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>10</AMOUNT>
    <CREDIT_CARD>VISA</CREDIT_CARD>
  </Z1PAYDTL>
  <Z1PAYDTL>
    <AMOUNT>40</AMOUNT>
    <CREDIT_CARD>MASTER</CREDIT_CARD>
  </Z1PAYDTL>
</Z1PAYHDR>

And the XSLT is:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl xsl" version="1.0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:key name="DetailCC" match="Z1PAYDTL" use="CREDIT_CARD" />
  <xsl:template match="Z1PAYHDR">
    <Output>
      <xsl:for-each select="Z1PAYDTL[count(. | key('DetailCC', CREDIT_CARD)[1]) = 1]">
        <xsl:variable name="card" select="CREDIT_CARD" />
        <CC>
          <Card>
            <xsl:value-of select="$card" />
          </Card>
          <TotalAmount>
            <xsl:value-of select="sum(//Z1PAYDTL[CREDIT_CARD = $card]/AMOUNT)" />
          </TotalAmount>
        </CC>
      </xsl:for-each>
    </Output>
  </xsl:template>
</xsl:stylesheet>

You will get the folllowing output:

<Output>
  <CC>
    <Card>VISA</Card>
    <TotalAmount>1110</TotalAmount>
  </CC>
  <CC>
    <Card>MASTER</Card>
    <TotalAmount>50</TotalAmount>
  </CC>
</Output>

Morten la Cour

February 12th, 2015 6:27am

This topic is archived. No further replies will be accepted.