Please help in solving this map. Here I have XML with HDR, DTL, FTR; The DTL have ITEM-BANK, ITEM-BANK, ITEM-BANK detail in pair together. I need to flatten this to output in one record.

BUSINESS RULE:

• 1) If ID element is empty it is then COMMENT and will be aggregated "Address of Party + Zipcode of Party + Postbus of Party" in COMMENT output XML.
• 2) If ID is BANK then it has bank details, and TAG element ID has ID value refer to above ITEM.

XSD POCProducts : http://pastebin.com/XNaq40vZ

POC MAP

EXPECTED/WANTED OUTOUT:

<ns0:PRODUCTS xmlns:ns0="http://TEST.Schema.FlatFile.POCProducts">
  <PRODUCT ProdName="Wonder Power" ProdDesc="Wonder power  DESC">
    <ITEMS>
      <ITEMS>
        <ID>1010</ID>
        <CODE>KG</CODE>
        <UNIT>C0001</UNIT>
        <DESC>Wonder Power 10*10ml</DESC>
        <BankCode>A1</BankCode>
        <BankDesc>Bank XXXX Code</BankDesc>
      </ITEMS>
      <ITEMS>
        <ID>2020</ID>
        <CODE>KG</CODE>
        <UNIT>C0001</UNIT>
        <DESC>Wonder Supper 50*50ml</DESC>
        <BankCode>A2</BankCode>
        <BankDesc>Bank yyyyy Code</BankDesc>
      </ITEMS>
    </ITEMS>
    <VAT>5390</VAT>
    <AMT>880099</AMT>
    <Comment>Address of Party + Zipcode of Party + Postbus of Party</Comment>
  </PRODUCT>
</ns0:PRODUCTS>

CURRENT OUTPUT XML:

<ns0:PRODUCTS xmlns:ns0="http://TEST.Schema.FlatFile.POCProducts">
  <PRODUCT ProdName="Wonder Power" ProdDesc="Wonder power  DESC">
    <ITEMS>
      <ITEMS>
        <ID>1010</ID>
        <CODE>KG</CODE>
        <UNIT>C0001</UNIT>
        <DESC>Wonder Power 10*10ml</DESC>
      </ITEMS>
      <ITEMS>
        <ID>2020</ID>
        <CODE>KG</CODE>
        <UNIT>C0001</UNIT>
        <DESC>Wonder Supper 50*50ml</DESC>
      </ITEMS>
    </ITEMS>
    <VAT>5390</VAT>
    <AMT>880099</AMT>
  </PRODUCT>
</ns0:PRODUCTS>

INPUT XML:

<POC xmlns="http://TEST.Schema.FlatFile.POC">
  <HDR xmlns="">
    <ProdName>Wonder Power</ProdName>
    <ProdDesc>Wonder power DESC</ProdDesc>
    <Filler></Filler>
  </HDR>
  <DTL xmlns="">
    <ID>1010</ID>
    <CODE>KG</CODE>
    <TAG>C0001</TAG>
    <DESC>Wonder Power 10*10ml</DESC>
    <FILLER></FILLER>
  </DTL>
  <DTL xmlns="">
    <ID>BANK</ID>
    <CODE>A1</CODE>
    <TAG>1010</TAG>
    <DESC>Bank XXXX Code</DESC>
    <FILLER></FILLER>
  </DTL>
  <DTL xmlns="">
    <ID>2020</ID>
    <CODE>KG</CODE>
    <TAG>C0001</TAG>
    <DESC>Wonder Supper 50*50ml</DESC>
    <FILLER></FILLER>
  </DTL>
  <DTL xmlns="">
    <ID>BANK</ID>
    <CODE>A2</CODE>
    <TAG>2020</TAG>
    <DESC>Bank yyyyy Code</DESC>
    <FILLER></FILLER>
  </DTL>
  <DTL xmlns="">
    <ID></ID>
    <CODE></CODE>
    <TAG>Address of Party</TAG>
    <DESC></DESC>
    <FILLER></FILLER>
  </DTL>
  <DTL xmlns="">
    <ID></ID>
    <CODE></CODE>
    <TAG></TAG>
    <DESC>Zipcode of Party</DESC>
    <FILLER></FILLER>
  </DTL>
  <DTL xmlns="">
    <ID></ID>
    <CODE></CODE>
    <TAG></TAG>
    <DESC>Postbus of Party</DESC>
    <FILLER></FILLER>
  </DTL>
  <FTR xmlns="">
    <VAT>5390</VAT>
    <TOTAL>880099</TOTAL>
    <FILLER></FILLER>
  </FTR>
</POC>

Thanks

• Edited by NimAnsari 19 hours 45 minutes ago

The aggregation of Comments still needs some work, but this Custom XSLT should get you started:

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0="http://TEST.Schema.FlatFile.POCProducts" xmlns:source="http://TEST.Schema.FlatFile.POC">
  <xsl:template match="source:POC">
    <ns0:PRODUCT>
      <ITEMS>
        <xsl:for-each select="DTL[(ID !=  'BANK') and (string-length(normalize-space(ID)) &gt; 0)]">
          <xsl:variable name="ID" select="ID" />
          <ITEMS>
            <ID>
              <xsl:value-of select="$ID" />
            </ID>
            <CODE>
              <xsl:value-of select="CODE" />
            </CODE>
            <UNIT>
              <xsl:value-of select="TAG" />
            </UNIT>
            <DESC>
              <xsl:value-of select="DESC" />
            </DESC>
            <xsl:variable name="bankcode" select="//DTL[(ID = 'BANK') and (TAG = $ID)]/CODE" />
            <xsl:if test="string-length($bankcode) &gt; 0">
              <BankCode>
                <xsl:value-of select="$bankcode" />
              </BankCode>
            </xsl:if>
            <xsl:variable name="bankdesc" select="//DTL[(ID = 'BANK') and (TAG = $ID)]/DESC" />
            <xsl:if test="string-length($bankdesc) &gt; 0">
              <BankDesc>
                <xsl:value-of select="$bankdesc" />
              </BankDesc>
            </xsl:if>
          </ITEMS>
        </xsl:for-each>
      </ITEMS>
      <VAT>
        <xsl:value-of select="FTR/VAT" />
      </VAT>
      <AMT>
        <xsl:value-of select="FTR/TOTAL" />
      </AMT>
      <Comment>
        <xsl:for-each select="DTL[string-length(normalize-space(ID)) = 0]">
          <xsl:value-of select="DESC" />
          <xsl:text> + </xsl:text>
        </xsl:for-each>
      </Comment>
    </ns0:PRODUCT>
  </xsl:template>
</xsl:stylesheet>

Morten la Cour

 

Hi NimAnsari,

For this requirement, I'll use XSLT in the map rather than using standard fuctionds. For your requirement use the following XSLT to get the desired output as you want..

Note: You may have to change the namespace to suit your schema definition.

<?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:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var s0" version="1.0" xmlns:ns0="http://TEST.Schema.FlatFile.POCProducts" xmlns:s0="http://TEST.Schema.FlatFile.POC">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/s0:POC" />
  </xsl:template>
  <xsl:template match="/s0:POC">
    <ns0:PRODUCTS>
      <PRODUCT>  <xsl:attribute name="ProdName">
          <xsl:value-of select="HDR/ProdName/text()" />
        </xsl:attribute>
        <xsl:attribute name="ProdDesc">
          <xsl:value-of select="HDR/ProdDesc/text()" />
        </xsl:attribute>
        <ITEMS>
          <xsl:for-each select="DTL[(ID!='BANK') and (ID!='')]">
            <ITEMS>
              <ID>
                <xsl:value-of select="ID/text()" />
              </ID>
              <CODE>
                <xsl:value-of select="CODE/text()" />
              </CODE>
              <UNIT>
                <xsl:value-of select="TAG/text()" />
              </UNIT>
              <DESC>
                <xsl:value-of select="DESC/text()" />
              </DESC>
              <BankCode>
                <xsl:value-of select="following-sibling::DTL[ID='BANK']/CODE"/>
              </BankCode>
              <BankDesc>
                <xsl:value-of select="following-sibling::DTL[ID='BANK']/DESC"/>
              </BankDesc>
            </ITEMS>
          </xsl:for-each>
        </ITEMS>
        <VAT>
          <xsl:value-of select="FTR/VAT/text()" />
        </VAT>
        <AMT>
          <xsl:value-of select="FTR/TOTAL/text()" />
        </AMT>
        
        <Comment>
        <!--Following code appends the values of DESC with '+ ' when ID values is empty-->  
          <xsl:for-each select="DTL[ID='']">
            <xsl:choose>
              <xsl:when test="position() = 1">
                <xsl:value-of select="DESC/text()"/>
              </xsl:when>
              <xsl:otherwise>
                + <xsl:value-of select="DESC/text()"/>
              </xsl:otherwise>
            </xsl:choose>
          </xsl:for-each>
        </Comment>
      </PRODUCT>
    </ns0:PRODUCTS>
  </xsl:template>
</xsl:stylesheet>

FYI, in the input instance given, the value Address of Party comes as part of the <TAG> not in <DESC>. I think it was a typo from your end. For the above given exact input, this XSLT will produce the out as following:

Regards,

M.R.Ashwin Prabhu