BizTalk Mapping – Inline XSLT usage

The BizTalk Mapper has many functoids that are capable of transforming operations. There are different type of functoids such as Advanced functoids(Value mapping, Table Looping, Assert etc.), String functoids.

2

 

In some cases, the functoids that are listed in toolbox are not capable of solving our transformation issues such as sorting, grouping etc. In order to solve complex and advance transformation issues we have to use inline xslt using with scripting functoid. It is shown in figure below.

1

 

I have tried to list most used xslt commands and i have used following source xml in the examples.


SOURCE XML
----------
<ns0:Root xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003/aggschema">
 <InputMessagePart_0>
  <ns1:OrderDoc xmlns:ns1="http://MessageEnrichment.Schema.OrderDoc">
   <Id>10</Id>
   <CustomerName>CustomerName_0</CustomerName>
  </ns1:OrderDoc>
 </InputMessagePart_0>
 <InputMessagePart_1>
  <ns2:GetOrderDetailsResponse xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
   <ns2:StoredProcedureResultSet0>
    <ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
     <ns3:OrderId>10</ns3:OrderId>
     <ns3:OrderItemId>10</ns3:OrderItemId>
     <ns3:ProductId>10</ns3:ProductId>
     <ns3:Quantity>10</ns3:Quantity>
     <ns3:Name>10_Name</ns3:Name>
     <ns3:Description>10_Description</ns3:Description>
     <ns3:Price>10.4</ns3:Price>
    </ns3:StoredProcedureResultSet0>
    <ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
     <ns3:OrderId>11</ns3:OrderId>
     <ns3:OrderItemId>11</ns3:OrderItemId>
     <ns3:ProductId>11</ns3:ProductId>
     <ns3:Quantity>11</ns3:Quantity>
     <ns3:Name>11_Name</ns3:Name>
     <ns3:Description>11_Description</ns3:Description>
     <ns3:Price>11.4</ns3:Price>
    </ns3:StoredProcedureResultSet0>
    <ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
     <ns3:OrderId>12</ns3:OrderId>
     <ns3:OrderItemId>12</ns3:OrderItemId>
     <ns3:ProductId>12</ns3:ProductId>
     <ns3:Quantity>12</ns3:Quantity>
     <ns3:Name>12_Name</ns3:Name>
    <ns3:Description>12_Description</ns3:Description>
    <ns3:Price>12.4</ns3:Price>
    </ns3:StoredProcedureResultSet0>
  </ns2:StoredProcedureResultSet0>
  <ns2:ReturnValue>15</ns2:ReturnValue>
  </ns2:GetOrderDetailsResponse>
 </InputMessagePart_1>
</ns0:Root>

 

**********************************************************************
                      ASSIGN SELECTED TO VARIABLE
**********************************************************************

XSLT
------
<xsl:variable name="vOrderName"
              xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"
              xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails"
              select="//InputMessagePart_1/ns2:GetOrderDetailsResponse/ns2:StoredProcedureResultSet0/ns3:StoredProcedureResultSet0" />

<OrderName>
  <xsl:for-each select="$vOrderName" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
    <Name>
      <xsl:element name="Description"><xsl:value-of select="ns3:Description"/></xsl:element>
      <xsl:element name="Quantity"><xsl:value-of select="ns3:Quantity"/></xsl:element>
      <xsl:element name="Price"><xsl:value-of select="ns3:Price"/></xsl:element>
      <xsl:element name="OrderItemId"><xsl:value-of select="ns3:OrderItemId"/></xsl:element>
    </Name>
  </xsl:for-each>
</OrderName>

&nbsp;

TARGET XML
----------
<OrderName>
  <Name>
    <Description>10_Description</Description>
    <Quantity>10</Quantity>
    <Price>10.4</Price>
    <OrderItemId>10</OrderItemId>
  </Name>
  <Name>
    <Description>11_Description</Description>
    <Quantity>11</Quantity>
    <Price>11.4</Price>
    <OrderItemId>11</OrderItemId>
  </Name>
  <Name>
    <Description>12_Description</Description>
    <Quantity>12</Quantity>
    <Price>12.4</Price>
    <OrderItemId>12</OrderItemId>
  </Name>
</OrderName>

 

**********************************************************************
                                 IF
**********************************************************************

XSLT
------
<xsl:variable name="vOrderName"
              xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"
              xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails"
              select="//InputMessagePart_1/ns2:GetOrderDetailsResponse/ns2:StoredProcedureResultSet0" />

<OrderName>
  <xsl:for-each select="$vOrderName" xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
  <StoredProcedureResultSet0_ns2>
    <xsl:if test="count(ns2:StoredProcedureResultSet0)>0">
      Node sayisi <xsl:value-of select="count(ns2:StoredProcedureResultSet0)"/>
    </xsl:if>
  </StoredProcedureResultSet0_ns2>
  <StoredProcedureResultSet0_ns3>
    <xsl:if test="count(ns3:StoredProcedureResultSet0)>0">
      Node sayisi <xsl:value-of select="count(ns3:StoredProcedureResultSet0)"/>
    </xsl:if>
  </StoredProcedureResultSet0_ns3>
 </xsl:for-each>
</OrderName>

&nbsp;

TARGET XML
----------
<OrderName>
  <StoredProcedureResultSet0_ns2 />
  <StoredProcedureResultSet0_ns3>
    Node sayisi 3
  </StoredProcedureResultSet0_ns3>
</OrderName>

 

**********************************************************************
                            CHOOSE, WHEN
**********************************************************************

XSLT
------
<xsl:variable name="vOrderName"
              xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"
              xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails"
              select="//InputMessagePart_1/ns2:GetOrderDetailsResponse/ns2:StoredProcedureResultSet0/ns3:StoredProcedureResultSet0" />

<OrderName>
  <xsl:for-each select="$vOrderName" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
    <xsl:variable name="quantityValue" select="ns3:Quantity" />
    <xsl:if test="$quantityValue=10">
      <xsl:element name="Quantity">If case - <xsl:value-of select="ns3:Quantity"/></xsl:element>
    </xsl:if>
    <xsl:choose>
      <xsl:when test="$quantityValue>10">
        <xsl:element name="Quantity"><xsl:value-of select="ns3:Quantity"/></xsl:element>
      </xsl:when>
      <xsl:otherwise>
        <xsl:element name="Quantity">Not enough - <xsl:value-of select="$quantityValue" /></xsl:element>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:for-each>
</OrderName>

&nbsp;

TARGET XML
----------
<OrderName>
  <Quantity>If case - 10</Quantity>
  <Quantity>Not enough - 10</Quantity>
  <Quantity>11</Quantity>
  <Quantity>12</Quantity>
</OrderName>

 

**********************************************************************
                                SORTING
**********************************************************************

XSLT
------
<xsl:variable name="vOrderName"
              xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"
              xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails"
              select="//InputMessagePart_1/ns2:GetOrderDetailsResponse/ns2:StoredProcedureResultSet0/ns3:StoredProcedureResultSet0" />

<OrderName>
  <xsl:for-each select="$vOrderName" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
    <xsl:sort select="ns3:Description" order="descending"/>
    <xsl:element name="Description"><xsl:value-of select="ns3:Description"/></xsl:element>
  </xsl:for-each>
</OrderName>

TARGET XML
----------
<OrderName>
  <Description>12_Description</Description>
  <Description>11_Description</Description>
  <Description>10_Description</Description>
</OrderName>

XSLT
------
<xsl:variable name="vOrderName"
              xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"
              xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails"
              select="//InputMessagePart_1/ns2:GetOrderDetailsResponse/ns2:StoredProcedureResultSet0/ns3:StoredProcedureResultSet0" />

<OrderName>
  <xsl:for-each select="$vOrderName" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
    <xsl:sort select="ns3:Quantity" order="descending" data-type="number"/>
    <xsl:element name="Quantity"><xsl:value-of select="ns3:Quantity"/></xsl:element>
  </xsl:for-each>
</OrderName>

TARGET XML
----------
<OrderName>
  <Quantity>12</Quantity>
  <Quantity>11</Quantity>
  <Quantity>10</Quantity>
</OrderName>

 


**********************************************************************
                             GROUPING
**********************************************************************

SOURCE XML
-----------
<ns0:Root xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003/aggschema">
<InputMessagePart_0>
<ns1:OrderDoc xmlns:ns1="http://MessageEnrichment.Schema.OrderDoc">
<Id>10</Id>
<CustomerName>CustomerName_0</CustomerName>
</ns1:OrderDoc>
</InputMessagePart_0>
<InputMessagePart_1>
<ns2:GetOrderDetailsResponse xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
<ns2:StoredProcedureResultSet0>
<ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
<ns3:OrderId>10</ns3:OrderId>
<ns3:OrderItemId>10</ns3:OrderItemId>
<ns3:ProductId>10</ns3:ProductId>
<ns3:Quantity>10</ns3:Quantity>
<ns3:Name>10_Name</ns3:Name>
<ns3:Description>10_Description</ns3:Description>
<ns3:Price>10.4</ns3:Price>
</ns3:StoredProcedureResultSet0>
<ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
<ns3:OrderId>10</ns3:OrderId>
<ns3:OrderItemId>10</ns3:OrderItemId>
<ns3:ProductId>10</ns3:ProductId>
<ns3:Quantity>10</ns3:Quantity>
<ns3:Name>10_Name</ns3:Name>
<ns3:Description>10_Description</ns3:Description>
<ns3:Price>10.4</ns3:Price>
</ns3:StoredProcedureResultSet0>
<ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
<ns3:OrderId>11</ns3:OrderId>
<ns3:OrderItemId>11</ns3:OrderItemId>
<ns3:ProductId>11</ns3:ProductId>
<ns3:Quantity>11</ns3:Quantity>
<ns3:Name>11_Name</ns3:Name>
<ns3:Description>11_Description</ns3:Description>
<ns3:Price>11.4</ns3:Price>
</ns3:StoredProcedureResultSet0>
<ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
<ns3:OrderId>11</ns3:OrderId>
<ns3:OrderItemId>11</ns3:OrderItemId>
<ns3:ProductId>11</ns3:ProductId>
<ns3:Quantity>11</ns3:Quantity>
<ns3:Name>11_Name</ns3:Name>
<ns3:Description>11_Description</ns3:Description>
<ns3:Price>11.4</ns3:Price>
</ns3:StoredProcedureResultSet0>
<ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
<ns3:OrderId>12</ns3:OrderId>
<ns3:OrderItemId>12</ns3:OrderItemId>
<ns3:ProductId>12</ns3:ProductId>
<ns3:Quantity>12</ns3:Quantity>
<ns3:Name>12_Name</ns3:Name>
<ns3:Description>12_Description</ns3:Description>
<ns3:Price>12.4</ns3:Price>
</ns3:StoredProcedureResultSet0>
<ns3:StoredProcedureResultSet0 xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails">
<ns3:OrderId>13</ns3:OrderId>
<ns3:OrderItemId>13</ns3:OrderItemId>
<ns3:ProductId>13</ns3:ProductId>
<ns3:Quantity>13</ns3:Quantity>
<ns3:Name>13_Name</ns3:Name>
<ns3:Description>13_Description</ns3:Description>
<ns3:Price>13.4</ns3:Price>
</ns3:StoredProcedureResultSet0>
</ns2:StoredProcedureResultSet0>
<ns2:ReturnValue>15</ns2:ReturnValue>
</ns2:GetOrderDetailsResponse>
</InputMessagePart_1>
</ns0:Root>

XSLT
----
<xsl:variable name="vStoredProcedureResultSet0"
              xmlns:ns2="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"
              xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails"
              select="//InputMessagePart_1/ns2:GetOrderDetailsResponse/ns2:StoredProcedureResultSet0/ns3:StoredProcedureResultSet0" />

<xsl:variable name="vQuantity"
              xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetOrderDetails"
              select="//ns3:StoredProcedureResultSet0[not(ns3:Quantity=preceding-sibling::ns3:StoredProcedureResultSet0/ns3:Quantity)]/ns3:Quantity" />

<OrderName>
  <xsl:for-each select="$vQuantity">
    <Quantity><xsl:value-of select="."/></Quantity>
  </xsl:for-each>
</OrderName>

TARGET XML
----------
<OrderName>
  <Quantity>10</Quantity>
  <Quantity>11</Quantity>
  <Quantity>12</Quantity>
  <Quantity>13</Quantity>
</OrderName>

 

**********************************************************************
                              PIVOT
**********************************************************************

SOURCE XML
----------

<Data Header="AAA" date="2008-10-28" Name="a1" Value="1.0" />
<Data Header="AAA" date="2008-10-28" Name="a2" Value="2.0" />
<Data Header="AAA" date="2008-10-28" Name="a3" Value="3.0" />
<Data Header="BBB" date="2008-10-28" Name="a1" Value="1.0" />
<Data Header="BBB" date="2008-10-28" Name="a2" Value="2.0" />
<Data Header="BBB" date="2008-10-28" Name="a3" Value="3.0" />

XSLT
------
<xsl:template name="NameValueTemplate">
  <xsl:param name="param1" />
  <xsl:for-each select="//Data[@Header=$param1]">
    <xsl:element name="Name"><xsl:value-of select="@Name" /></xsl:element>
    <xsl:element name="Value"><xsl:value-of select="@Value" /></xsl:element>
  </xsl:for-each>
</xsl:template>

<xsl:element name="Data">
  <xsl:for-each select="Data[not(@Header=preceding-sibling::Data/@Header)]">
    <xsl:element name="Header"><xsl:value-of select="@Header" /></xsl:element>
    <xsl:element name="date"><xsl:value-of select="@date" /></xsl:element>
    <xsl:element name="Record">
      <xsl:call-template name="NameValueTemplate">
        <xsl:with-param name="param1" select="string(@Header)" />
      </xsl:call-template>
    </xsl:element>
  </xsl:for-each>
</xsl:element>

&nbsp;

TARGET XML
----------
<Data>
  <Header>AAA</Header>
  <date>2008-10-28</date>
  <Record>
    <Name>a1</Name>
    <Value>1.0</Value>
    <Name>a2</Name>
    <Value>2.0</Value>
    <Name>a3</Name>
    <Value>3.0</Value>
  </Record>
  <Header>BBB</Header>
  <date>2008-10-28</date>
  <Record>
    <Name>a1</Name>
    <Value>1.0</Value>
    <Name>a2</Name>  
    <Value>2.0</Value>
    <Name>a3</Name>
    <Value>3.0</Value>
  </Record>
</Data>