Filter Unique Records Using BizTalk XSL

  • Requirement

I have a XML file which contains some duplicate records. The XML file looks like below:

<ns0:DataNode xmlns:ns0=”https://alamnaushad.wordpress.com/DataNode”>
    <ns0:MainNode>
        <ns0:ID>123</ns0:ID>
        <ns0:name>First Index</ns0:name>
    </ns0:MainNode>
    <ns0:MainNode>
        <ns0:ID>123</ns0:ID>
        <ns0:name>Second Index</ns0:name>
    </ns0:MainNode>
    <ns0:MainNode>
        <ns0:ID>123</ns0:ID>
        <ns0:name>Last Index</ns0:name>
    </ns0:MainNode>
    <ns0:MainNode>
        <ns0:ID>1234</ns0:ID>
        <ns0:name>First Index</ns0:name>
    </ns0:MainNode>
</ns0:DataNode>

The requirement here to get only unique records based on the value of ID element. Also We have to get the last occurrence of ManinNode for which ID is unique in the XML.

After the MAP Output should be like below XML

<ns0:DataNode xmlns:ns0=”https://alamnaushad.wordpress.com/DataNode“>

<ns0:MainNode>

<ns0:ID>123</ns0:ID>

<ns0:name>Last Index</ns0:name>

</ns0:MainNode>

<ns0:MainNode>

<ns0:ID>1234</ns0:ID>

<ns0:name>First Index</ns0:name>

</ns0:MainNode>

</ns0:DataNode>

  • Solution Approach

    I would write a custom XSL to achieve the above requirement . To get the last occurrence of from a node set I would use last() xsl function.

  • Code

      I would perform following steps to create a MAP and use the XSL in MAP to achieve my requirement.  My XSL code looks like below
         

      <?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” version=”1.0″ xmlns:ns0=”https://alamnaushad.wordpress.com/DataNode”>
          <xsl:output omit-xml-declaration=”yes” method=”xml” version=”1.0″ />

          <xsl:key name=”item-by-value” match=”ns0:ID” use=”.”/>
          <xsl:template match=”/”>
              <ns0:DataNode>
              <xsl:apply-templates select=”/ns0:DataNode/ns0:MainNode/ns0:ID” />
              </ns0:DataNode>
          </xsl:template>
              <xsl:template match=”ns0:ID”>
                  <xsl:for-each select=”.”>
                      <xsl:if test=”generate-id() = generate-id(key(‘item-by-value’, normalize-space(.))[last()])”>
                      <ns0:MainNode>
                          <ns0:ID>
                              <xsl:value-of select=”.” />
                          </ns0:ID>
                          <ns0:name>
                              <xsl:value-of select=”following-sibling::*”/>
                          </ns0:name>
                      </ns0:MainNode>
                      </xsl:if>
                  </xsl:for-each>
          </xsl:template>
      </xsl:stylesheet>

    1. Created a Schema to hold the input xml message
    2. Created A Map to transform the input to desired output
    3. Created a XSL file which contains all my transformation logic
    4. Set the property of BizTalk Map to use the custom XSL file Path like below.

    2-24-2012 12-23-18 AM

    I have uploaded the complete project here, Please download and feel free to use it!!

     

      Advertisements

      About Naushad Alam
      I am a BizTalk developer/administrator/designer/architect with over 9 years of experience. Currently I am living in UK and working with one of the largest bank as a Senior BizTalk Consultant. I believe in sharing my experience with others and learn more while helping or solving others problem.

      3 Responses to Filter Unique Records Using BizTalk XSL

      1. pavan says:

        Thanks Naushad,I have a small doubt here,Suppose if have some more nodes in the MainNode, if some of them are optional, am getting an error.If I remove 2nd element node, which is optional in my schema.Then the 3rd element data is being copied to 2nd element.

      Leave a Reply

      Fill in your details below or click an icon to log in:

      WordPress.com Logo

      You are commenting using your WordPress.com account. Log Out / Change )

      Twitter picture

      You are commenting using your Twitter account. Log Out / Change )

      Facebook photo

      You are commenting using your Facebook account. Log Out / Change )

      Google+ photo

      You are commenting using your Google+ account. Log Out / Change )

      Connecting to %s

      %d bloggers like this: