Checking for Duplicate values in map

Home Page Forums BizTalk 2004 – BizTalk 2010 Checking for Duplicate values in map

Viewing 1 reply thread
  • Author
    Posts
    • #22791

      Hi,

      I am a newbie.I have multiple child records and in my map I want to check for duplicate fields of each record. I was trying to use the looping functoid but having difficulty. Does anyone have a good solution for this?

      Regards,

      Susan

    • #22795

      Hi Susan,

      Here is an example, let say I have this Employees schema, which contains multiple Employee record.

      —————————————————————————————————-

       

      <?xml version=”1.0″ encoding=”utf-16″?>
      <xs:schema xmlns:b=”http://schemas.microsoft.com/BizTalk/2003&#8243; xmlns=”http://BizTalk_Server_Project1.Employees&#8221; targetNamespace=”http://BizTalk_Server_Project1.Employees&#8221; xmlns:xs=”http://www.w3.org/2001/XMLSchema”&gt;
        <xs:element name=”Employees”>
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs=”1″ maxOccurs=”unbounded” name=”Employee”>
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name=”FirstName” type=”xs:string” />
                    <xs:element name=”LastName” type=”xs:string” />
                    <xs:element name=”RecordID” type=”xs:string” />
                    <xs:element name=”UserID” type=”xs:string” />
                    <xs:element name=”Address” type=”xs:string” />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>

      —————————————————————————————————-

       

       

      Some of the Employee record might have been duplicated, therefore I want to remove the duplicate record base on the UserID. Hence i created a map that maps an Employees msg to an Employees message and use a custom XSL file for the map (see here for an example of how to use custom XSL for map: https://www.biztalkgurus.com/forums/p/12513/24762.aspx#24762

       

      Let say I have this input message:

      ————————————————————————————————————-

      <?xml version=”1.0″ encoding=”utf-8″?>
      <ns0:Employees xmlns:ns0=”http://BizTalk_Server_Project1.Employees”&gt;
      <Employee>
      <FirstName>FirstName_0</FirstName>
      <LastName>LastName_0</LastName>
      <RecordID>RecordID_0</RecordID>
      <UserID>UserID_0</UserID>
      <Address>Address_0</Address>
      </Employee>
      <Employee>
      <FirstName>FirstName_1</FirstName>
      <LastName>LastName_1</LastName>
      <RecordID>RecordID_1</RecordID>
      <UserID>UserID_1</UserID>
      <Address>Address_1</Address>
      </Employee>
      <Employee>
      <FirstName>FirstName_0</FirstName>
      <LastName>LastName_0</LastName>
      <RecordID>RecordID_0</RecordID>
      <UserID>UserID_0</UserID>
      <Address>Address_0</Address>
      </Employee>
      <Employee>
      <FirstName>FirstName_2</FirstName>
      <LastName>LastName_2</LastName>
      <RecordID>RecordID_2</RecordID>
      <UserID>UserID_2</UserID>
      <Address>Address_2</Address>
      </Employee>
      <Employee>
      <FirstName>FirstName_1</FirstName>
      <LastName>LastName_1</LastName>
      <RecordID>RecordID_1</RecordID>
      <UserID>UserID_1</UserID>
      <Address>Address_1</Address>
      </Employee>
      </ns0:Employees>

       

       

      ————————————————————————————————————-

       

       

      I use this custom XSL to transform the message:

       

      ————————————————————————————————————-

       

       

       

      <?xml version=”1.0″ encoding=”UTF-8″ ?> 
      <xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform&#8221; xmlns:msxsl=”urn:schemas-microsoft-com:xslt” xmlns:var=”http://schemas.microsoft.com/BizTalk/2003/var&#8221; exclude-result-prefixes=”msxsl var” version=”1.0″ xmlns:ns0=”http://BizTalk_Server_Project1.Employees”&gt;
        <xsl:output omit-xml-declaration=”yes” method=”xml” version=”1.0″ /> 
         <xsl:template match=”/”>
      <xsl:copy>
      <xsl:apply-templates select=”/ns0:Employees”/>
      </xsl:copy>
      </xsl:template>
      <xsl:template match=”/ns0:Employees”>
         <xsl:copy>
       <xsl:apply-templates select=”Employee”>
      <xsl:sort select=”UserID” order=”ascending”/>
       </xsl:apply-templates>
      </xsl:copy>
      </xsl:template>
      <xsl:template match=”Employee”>
      <xsl:if test=”not(UserID = preceding-sibling::Employee/UserID)”>
      <xsl:copy-of select=”.”/>
         </xsl:if>
        </xsl:template>
        </xsl:stylesheet>

       

      ————————————————————————————————————————

      and here is the output message:

      ————————————————————————————————————————

      <?xml version=”1.0″ encoding=”utf-8″?>
      <ns0:Employees xmlns:ns0=”http://BizTalk_Server_Project1.Employees”&gt;
      <Employee>
      <FirstName>FirstName_0</FirstName>
      <LastName>LastName_0</LastName>
      <RecordID>RecordID_0</RecordID>
      <UserID>UserID_0</UserID>
      <Address>Address_0</Address>
      </Employee>
      <Employee>
      <FirstName>FirstName_1</FirstName>
      <LastName>LastName_1</LastName>
      <RecordID>RecordID_1</RecordID>
      <UserID>UserID_1</UserID>
      <Address>Address_1</Address>
      </Employee>
      <Employee>
      <FirstName>FirstName_2</FirstName>
      <LastName>LastName_2</LastName>
      <RecordID>RecordID_2</RecordID>
      <UserID>UserID_2</UserID>
      <Address>Address_2</Address>
      </Employee>
      </ns0:Employees>

       

       

      ————————————————————————————————————-

      Let me know if that helps

       

       

      • #22798

        While Guo Ming Li’s will work, you will start to have performance issues as the number of records grows.

        For each employee record you will have to scan all the preceding records i.e

        1 record = 0 preceding
        2 records = 1 preceding
        3 records = 1 + 2 preceding
        4 records = 1 + 2 + 3 preceding
        5 records = 1 + 2 + 3 + 4 preceding

        The total number of record checks is calculated by the function n(n-1)/2, so for 1000 records you will need to check  499500 preceding records.

        There is an example here https://www.biztalkgurus.com/forums/p/9947/19832.aspx#19832 that uses the Xslt Key – sort of a modified Muenchian grouping pattern.

        Alternatively you could use a scripting functoid that uses a global StringCollection variable:

        System.Collections.Specialized.StringCollection uniqueIds = new System.Collections.Specialized.StringCollection() ;

        public bool IsUniqueId(string id)
        {
            if (uniqueIds.Contains(id))
                  return false;
             uniqueIds.Add(id);
             return true;
        }

        Put this scripting functoid in front of a Value Mapping functoid.

        • #22837

          Hi Greg, that’s genius solution, with your suggestion, I was able to do the same thing by modifying your example slightly:

           

          <xsl:stylesheet xmlns:ns0=”http://BizTalk_Server_Project1.Employees&#8221; xmlns:xsl=”http://www.w3.org/1999/XSL/Transform&#8221; xmlns:msxsl=”urn:schemas-microsoft-com:xslt” xmlns:var=”http://schemas.microsoft.com/BizTalk/2003/var&#8221; exclude-result-prefixes=”msxsl var” version=”1.0″>
          <xsl:output omit-xml-declaration=”yes” method=”xml” version=”1.0″ />
          <xsl:key name=”duplicateKey” match=”Employee/UserID” use=”.” />
          <xsl:template match=”/”>
          <xsl:apply-templates select=”/ns0:Employees” />
          </xsl:template>
          <xsl:template match=”/ns0:Employees”>
          <xsl:variable name=”Employees”>
          <xsl:for-each select=”Employee/UserID”>
          <xsl:variable name=”group” select=”key(‘duplicateKey’,.)” />
          <xsl:if test=”generate-id($group[1]) = generate-id()”>
          <xsl:if test=”count($group) &lt; 2 or count($group) &gt; 1″>
          <xsl:element name=”Employee”>
          <xsl:element name=”UserID”>
          <xsl:value-of select=”$group[1]” />
          </xsl:element>
          <xsl:element name=”FirstName”>
          <xsl:value-of select=”/ns0:Employees/Employee[UserID=$group[1]]/FirstName” />
          </xsl:element>
          <xsl:element name=”LastName”>
          <xsl:value-of select=”/ns0:Employees/Employee[UserID=$group[1]]/LastName” />
          </xsl:element>
          <xsl:element name=”RecordID”>
          <xsl:value-of select=”/ns0:Employees/Employee[UserID=$group[1]]/RecordID” />
          </xsl:element>
          <xsl:element name=”Address”>
          <xsl:value-of select=”/ns0:Employees/Employee[UserID=$group[1]]/Address” />
          </xsl:element>
          </xsl:element>
          </xsl:if>
          </xsl:if>
          </xsl:for-each>
          </xsl:variable>
          <xsl:choose>
          <xsl:when test=”count(msxsl:node-set($Employees)/Employee/UserID) &gt; 0″>
          <xsl:element name=”Employees” namespace=”http://BizTalk_Server_Project1.Employees”&gt;
          <xsl:copy-of select=”$Employees” />
          </xsl:element>
          </xsl:when>
          <xsl:otherwise>
          <xsl:copy-of select=”.” />
          </xsl:otherwise>
          </xsl:choose>
          </xsl:template>
          </xsl:stylesheet>

        • #23270

          I like this solution, but if you map the scripting functoid to a value mapping functoid, what exactly is mapped to the value mapping functoid? The script bool value and what else? It needs two inputs. Also, where does that get mapped to?

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.