none
How to Edit Existing XSLT Template in BizTalk Map?

    Question

  • This is My sample XSLT code. I want to capture last record from the duplicate nodes along with unique nodes.

    I created this XSLT from a existing Map.

    Note: I am using multiple schemas as imported collection to make a source & target schema structure.

    <?xml version="1.0" encoding="UTF-8"?>
    <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 s1 s2 s3 s0" version="1.0" xmlns:s0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo" xmlns:array="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/SIS/Sale" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/SIS" xmlns:s2="http://schemas.microsoft.com/BizTalk/2003/aggschema" xmlns:s1="http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT" xmlns:s3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/LoadingBatch" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
      <xsl:template match="/">
      <xsl:apply-templates select="/s2:Root" />
      </xsl:template>
      <xsl:template match="/s2:Root">
        <ns0:Insert>
          <ns0:Rows>
           <xsl:for-each select="InputMsgPart_B/s1:Mydata/s1:SaleData">
              <ns3:Sale>
          <xsl:if test="../../../InputMsgPart_A/s0:LoadingBatchResponse/s0:BatchCode">
              <ns3:BatchCode>
              </ns3:BatchCode>
              </xsl:if>
              <ns3:SaleID>
                  <xsl:value-of select="s1:Sale_ID/text()" />
                </ns3:SaleID>
                <ns3:CountryID>
                  <xsl:value-of select="s1:Country_ID/text()" />
                </ns3:CountryID>
                <ns3:ItemID>
                  <xsl:value-of select="s1:Item_ID/text()" />
                </ns3:ItemID>
                <ns3:ItemName>
                  <xsl:value-of select="s1:Item_Name/text()" />
                </ns3:ItemName>
                <ns3:Price>
                  <xsl:value-of select="s1:Item_price/text()" />
                </ns3:Price>
                 </ns3:Sale>
            </xsl:for-each>
          </ns0:Rows>
        </ns0:Insert>
      </xsl:template>
    </xsl:stylesheet>

    From Above sample I want to capture last record of <saleID> from duplicates

    <Sale> is the repeat node here. How I can use the Count(), Last() or Current() functions to capture the last duplicate?

    Thursday, February 23, 2012 7:35 PM

Answers

  • Hi

    This might work for you, Please check

    Input XML as:

    <ns0:SaleUpdates xmlns:ns0=http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT>
      <ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID1</ns0:Sale_ID>
        <ns0:Sale_Total>24503540</ns0:Sale_Total>
        <ns0:Country_Name>CountryOne</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID2</ns0:Sale_ID>
        <ns0:Sale_Total>24507890</ns0:Sale_Total>
        <ns0:Country_Name>CountryTwo</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Site_ID1</ns0:Sale_ID>
        <ns0:Sale_Total>24503544</ns0:Sale_Total>
        <ns0:Country_Name>CountryOne</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Site_ID3</ns0:Sale_ID>
        <ns0:Sale_Total>24563540</ns0:Sale_Total>
        <ns0:Country_Name>CountryThree</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Site_ID3</ns0:Sale_ID>
        <ns0:Sale_Total>24563000</ns0:Sale_Total>
        <ns0:Country_Name>CountryThree</ns0:Country_Name>
        </ns0:RetailRec>
    </ns0:SaleUpdates>

    XSL for Filtering and taking the last record is below:

    <?xml version="1.0" encoding="iso-8859-1"?>
    <xsl:stylesheet version="1.0"   xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0=http://w1.company.com/xsd/path/FOR/CRC/SIS/MyData/123/CPT>
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0"/>
      <xsl:key name="item-by-value" match="ns0:Sale_ID" use="."/>
      <xsl:template match="/">
        <ns0:SaleUpdates>
          <xsl:apply-templates select="/ns0:SaleUpdates/ns0:RetailRec/ns0:Sale_ID"/>
        </ns0:SaleUpdates>
      </xsl:template>
      <xsl:template match="ns0:Sale_ID">
        <xsl:for-each select=".">
          <xsl:if test="generate-id() = generate-id(key('item-by-value', normalize-space(.))[last()])">
            <ns0:RetailRec>
              <ns0:Sale_ID>
                <xsl:value-of select="."/>
              </ns0:Sale_ID>
              <ns0:Sale_Total>
                <xsl:value-of select="following-sibling::*[1]"/>
              </ns0:Sale_Total>
              <ns0:Country_Name>
                <xsl:value-of select="following-sibling::*[2]"/>
              </ns0:Country_Name>
              </ns0:RetailRec>
          </xsl:if>
        </xsl:for-each>
      </xsl:template>
    </xsl:stylesheet>

    And after running the MAP i got the following output:

    <ns0:SaleUpdates 
    xmlns:ns0="http://w1.company.com/xsd/path/FOR/CRC/SIS/MyData/123/CPT"><ns0:RetailRec><ns0:Sale_ID>Sale_I
    D1</ns0:Sale_ID><ns0:Sale_Total>24503540</ns0:Sale_Total><ns0:Country_Name>CountryOne</ns0:Country_Name></ns0:R
    etailRec><ns0:RetailRec><ns0:Sale_ID>Sale_ID2</ns0:Sale_ID><ns0:Sale_Total>24507890</ns0:Sale_Total><ns0:Countr
    y_Name>CountryTwo</ns0:Country_Name></ns0:RetailRec><ns0:RetailRec><ns0:Sale_ID>Site_ID1</ns0:Sale_ID><ns0:Sale
    _Total>24503544</ns0:Sale_Total><ns0:Country_Name>CountryOne</ns0:Country_Name></ns0:RetailRec><ns0:RetailRec><
    ns0:Sale_ID>Site_ID3</ns0:Sale_ID><ns0:Sale_Total>24563000</ns0:Sale_Total><ns0:Country_Name>CountryThree</ns0:
    Country_Name></ns0:RetailRec></ns0:SaleUpdates>

    I Hope it solves your problem. Please let me know if you need more details.

    HTH

    Naushad

    Please Mark If it is an answers & Please Vote if It was useful


    Friday, February 24, 2012 11:02 AM
  • Hi 

    I would suggest you to please refer this similar thread, I have posted the xslt code to get the unique and the last  xml element. Please have a look ,might help you.

    HTH

    Naushad

    Thursday, February 23, 2012 7:48 PM

All replies

  • Hi 

    I would suggest you to please refer this similar thread, I have posted the xslt code to get the unique and the last  xml element. Please have a look ,might help you.

    HTH

    Naushad

    Thursday, February 23, 2012 7:48 PM
  • Hi 

    Just to add , I have managed to do a blog post which provides solution of the above similar problem, It might be useful for you. Please see "http://alamnaushad.wordpress.com/2012/02/24/filter-unique-records-using-biztalk-xsl/

    HTH

    Naushad

    Friday, February 24, 2012 12:34 AM
  • Thanks for the replies...

    But in My case I am using multiple schemas with in a main schema by importing them into fist schema -- routed some dynamic content + static content

    Scenario is like this
    InputMsgPart_A
    {
     <DynamicNodes>
     +InputMsgPart_A
      {
        <Root>
        <MainNode>
        <ReapeatRecord>
         <Record></Record>
         </ReapeatRecord>
        <ReapeatRecord>
         <Record></Record>
         </ReapeatRecord>
         </MainNode>
       }
     +Trailer
     {
     }
    }

    Similarly as above I constructed Target schema with dynamc + static schemas contents.

    My Question is How I can use another XSL template below the Main node with out disturbing the top schema templattes

    * The XSL is generated from a existing Map.So I have to use it

    glad if some one solve the issue with appropreate notes & sample screens

    I will share here, If I succeeed first.

    Friday, February 24, 2012 3:13 AM
  • Hi 

    Is is possible if you could please send/put your schema or input xml ? It will be a help while writing the notes/xslt

    HTH,Thanks, Naushad (MCC/MCTS) http://alamnaushad.wordpress.com |@naushadalam

    If this is helpful or answers your question - please mark accordingly! Please "Vote As Helpful" if this was useful while resolving your question!

    Friday, February 24, 2012 8:20 AM
  • Thanks for the reply..

    I changed My Complex XSL  to simple. Now I am using different Maps to achieve the target. I am now using a map to eliminate duplicates.

    Now My Schema, XSL and Source Message as following...

    Schema Sample for Input & Output -- I am using same schema in both sides to copy the data

      <?xml version="1.0" encoding="utf-8" ?> 
    - <xs:schema xmlns:tns="http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    - <xs:element name="SaleUpdates">
    - <xs:complexType>
    - <xs:sequence>
    - <xs:element minOccurs="0" maxOccurs="unbounded" name="RetailRec">
    - <xs:complexType>
    - <xs:sequence>
    - <xs:element name="Sale_ID">
    - <xs:simpleType>
    - <xs:restriction base="xs:string">
      <xs:maxLength value="10" /> 
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
    - <xs:element name="Sale_Total">
    - <xs:simpleType>
    - <xs:restriction base="xs:string">
      <xs:maxLength value="12" /> 
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
    - <xs:element name="Country_Name">
    - <xs:simpleType>
    - <xs:restriction base="xs:string">
      <xs:maxLength value="25" /> 
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
    - </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:schema>

    After Creating MAP. I achieved this XSL....

      <?xml version="1.0" encoding="UTF-8" ?> 
    - <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="http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" /> 
    - <xsl:template match="/">
      <xsl:apply-templates select="/ns0:SaleUpdates" /> 
      </xsl:template>
    - <xsl:template match="/ns0:SaleUpdates">
    - <ns0:SaleUpdates>
    - <xsl:for-each select="ns0:RetailRec">
    - <ns0:RetailRec>
    - <ns0:Sale_ID>
      <xsl:value-of select="ns0:Sale_ID/text()" /> 
      </ns0:Sale_ID>
    - <ns0:Sale_Total>
      <xsl:value-of select="ns0:Sale_Total/text()" /> 
      </ns0:Sale_Total>
    - <ns0:Country_Name>
      <xsl:value-of select="ns0:Country_Name/text()" /> 
      </ns0:Country_Name>
    -</ns0:RetailRec>
      </xsl:for-each>
      </ns0:SiteUpdates>
      </xsl:template>
      </xsl:stylesheet>

    My Incoming Message XML....

    <ns0:SaleUpdates xmlns:ns0="http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT">
      <ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID1</ns0:Sale_ID>
        <ns0:Sale_Total>24503540</ns0:Sale_Total>
        <ns0:Country_Name>CountryOne</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID2</ns0:Sale_ID>
        <ns0:Sale_Total>24507890</ns0:Sale_Total>
        <ns0:Country_Name>CountryTwo</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID1</ns0:Sale_ID>
        <ns0:Sale_Total>24503544</ns0:Sale_Total>
        <ns0:Country_Name>CountryOne</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID3</ns0:Sale_ID>
        <ns0:Sale_Total>24563540</ns0:Sale_Total>
        <ns0:Country_Name>CountryThree</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID3</ns0:Sale_ID>
        <ns0:Sale_Total>24563000</ns0:Sale_Total>
        <ns0:Country_Name>CountryThree</ns0:Country_Name>
        </ns0:RetailRec>
    </ns0:SaleUpdates>

    Now I want to pickup unique records and Last record from message after eliminating duplicates...

    My Desired OUTPUT should be like this...

    <ns0:SaleUpdates xmlns:ns0="http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT">
     <ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID1</ns0:Sale_ID>
        <ns0:Sale_Total>24503544</ns0:Sale_Total>
        <ns0:Country_Name>CountryOne</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID2</ns0:Sale_ID>
        <ns0:Sale_Total>24507890</ns0:Sale_Total>
        <ns0:Country_Name>CountryTwo</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID3</ns0:Sale_ID>
        <ns0:Sale_Total>24563000</ns0:Sale_Total>
        <ns0:Country_Name>CountryThree</ns0:Country_Name>
        </ns0:RetailRec>
    </ns0:SaleUpdates>
    I am very glad and thankful if some one answered quickly...


    • Edited by Wolverine.XLogan Monday, February 27, 2012 10:38 AM changes incoming Msg
    Friday, February 24, 2012 10:47 AM
  • Hi

    This might work for you, Please check

    Input XML as:

    <ns0:SaleUpdates xmlns:ns0=http://w1.company.com/xsd/path/FOL/CRC/SIS/MyData/123/CPT>
      <ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID1</ns0:Sale_ID>
        <ns0:Sale_Total>24503540</ns0:Sale_Total>
        <ns0:Country_Name>CountryOne</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Sale_ID2</ns0:Sale_ID>
        <ns0:Sale_Total>24507890</ns0:Sale_Total>
        <ns0:Country_Name>CountryTwo</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Site_ID1</ns0:Sale_ID>
        <ns0:Sale_Total>24503544</ns0:Sale_Total>
        <ns0:Country_Name>CountryOne</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Site_ID3</ns0:Sale_ID>
        <ns0:Sale_Total>24563540</ns0:Sale_Total>
        <ns0:Country_Name>CountryThree</ns0:Country_Name>
        </ns0:RetailRec>
    	<ns0:RetailRec>
        <ns0:Sale_ID>Site_ID3</ns0:Sale_ID>
        <ns0:Sale_Total>24563000</ns0:Sale_Total>
        <ns0:Country_Name>CountryThree</ns0:Country_Name>
        </ns0:RetailRec>
    </ns0:SaleUpdates>

    XSL for Filtering and taking the last record is below:

    <?xml version="1.0" encoding="iso-8859-1"?>
    <xsl:stylesheet version="1.0"   xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0=http://w1.company.com/xsd/path/FOR/CRC/SIS/MyData/123/CPT>
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0"/>
      <xsl:key name="item-by-value" match="ns0:Sale_ID" use="."/>
      <xsl:template match="/">
        <ns0:SaleUpdates>
          <xsl:apply-templates select="/ns0:SaleUpdates/ns0:RetailRec/ns0:Sale_ID"/>
        </ns0:SaleUpdates>
      </xsl:template>
      <xsl:template match="ns0:Sale_ID">
        <xsl:for-each select=".">
          <xsl:if test="generate-id() = generate-id(key('item-by-value', normalize-space(.))[last()])">
            <ns0:RetailRec>
              <ns0:Sale_ID>
                <xsl:value-of select="."/>
              </ns0:Sale_ID>
              <ns0:Sale_Total>
                <xsl:value-of select="following-sibling::*[1]"/>
              </ns0:Sale_Total>
              <ns0:Country_Name>
                <xsl:value-of select="following-sibling::*[2]"/>
              </ns0:Country_Name>
              </ns0:RetailRec>
          </xsl:if>
        </xsl:for-each>
      </xsl:template>
    </xsl:stylesheet>

    And after running the MAP i got the following output:

    <ns0:SaleUpdates 
    xmlns:ns0="http://w1.company.com/xsd/path/FOR/CRC/SIS/MyData/123/CPT"><ns0:RetailRec><ns0:Sale_ID>Sale_I
    D1</ns0:Sale_ID><ns0:Sale_Total>24503540</ns0:Sale_Total><ns0:Country_Name>CountryOne</ns0:Country_Name></ns0:R
    etailRec><ns0:RetailRec><ns0:Sale_ID>Sale_ID2</ns0:Sale_ID><ns0:Sale_Total>24507890</ns0:Sale_Total><ns0:Countr
    y_Name>CountryTwo</ns0:Country_Name></ns0:RetailRec><ns0:RetailRec><ns0:Sale_ID>Site_ID1</ns0:Sale_ID><ns0:Sale
    _Total>24503544</ns0:Sale_Total><ns0:Country_Name>CountryOne</ns0:Country_Name></ns0:RetailRec><ns0:RetailRec><
    ns0:Sale_ID>Site_ID3</ns0:Sale_ID><ns0:Sale_Total>24563000</ns0:Sale_Total><ns0:Country_Name>CountryThree</ns0:
    Country_Name></ns0:RetailRec></ns0:SaleUpdates>

    I Hope it solves your problem. Please let me know if you need more details.

    HTH

    Naushad

    Please Mark If it is an answers & Please Vote if It was useful


    Friday, February 24, 2012 11:02 AM
  • Hi,

    I think this has already been answered in thread below,

    http://social.msdn.microsoft.com/Forums/en/biztalkgeneral/thread/0015f7ed-736f-4164-a262-f8db8121039a


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/

    Friday, February 24, 2012 11:08 AM
  • Thank you for your Efforts  Naushad.Alam

    Will update on this soon...

    Friday, February 24, 2012 12:53 PM
  • Thank You for your Efforts Naushad.Alam Its Working for my scenario...

    Good work... Once again I am glad to say thank you ...

    Just a small change is needed in your Answer in the namespave  -- Change FOR to FOL

    Tuesday, February 28, 2012 10:01 AM