BizTalk Services Mapper - WCF SQL Select Query - Cumulative Concatenate

Say you want to create a map in order to produce the SELECT statement for a WCF-SQL query and you start of with a list of values to include in the where clause.

How to achieve this with the BizTalk Services mapper?

The Source XML:

<ns0:GetProducts xmlns:ns0="http://GetProductsBizTalkService.Schemas">
  <Column>
    <Name>Name_0</Name>
    <Value>Value_0</Value>
  </Column>
  <Column>
    <Name>Name_1</Name>
    <Value>Value_1</Value>
  </Column>
  <Column>
    <Name>Name_2</Name>
    <Value>Value_2</Value>
  </Column>
</ns0:GetProducts>

The Destination XML:

<ns3:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns3:Columns>*</ns3:Columns>
  <ns3:Query>where Name_0='Value_0' and Name_1='Value_1' and Name_2='Value_2'</ns3:Query>
</ns3:Select>

First, how could we do this within XSLT?

This can be done with the following XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:s0="http://GetProductsBizTalkService.Schemas"
                xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products"
                exclude-result-prefixes="msxsl s0">
  <xsl:output method="xml"
              indent="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates select="/s0:GetProducts" />
  </xsl:template>
  <xsl:template match="/s0:GetProducts">
    <ns0:Select>
      <ns0:Columns>*</ns0:Columns>
      <xsl:if test="Column">
        <ns0:Query>
          <xsl:for-each select="Column">
            <xsl:if test='position() = 1'>where </xsl:if>
            <xsl:value-of select="Name"/>='<xsl:value-of select="Value"/>'
            <xsl:if test='position() != last()'> and </xsl:if>
          </xsl:for-each>
        </ns0:Query>
      </xsl:if>
    </ns0:Select>
  </xsl:template>
</xsl:stylesheet>

The result of the above will be:

<?xml version="1.0" encoding="utf-8"?>
<ns0:Select xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns0:Columns>*</ns0:Columns>
  <ns0:Query>
    where Name_0='Value_0'
    and Name_1='Value_1'
    and Name_2='Value_2'
  </ns0:Query>
</ns0:Select>

Finally, how to achieve the same thing with the mapper:



What did we do?

First of all we need a list and the list will contain the list of expressions based on the column name and values:

Name_0='Value_0'
Name_1='Value_1'
Name_2='Value_2'

For this we use the new "Create List" functoid:

include the "ForEach Loop" functoid - in order to loop the "Column" records -:

use a "String Concatenate" in combination with the "Add Item to List" functoid to produce the above list:


Now that we have the list, how to produce the query statement? "Cumulative Concatenate" comes to the rescue:


Since the "Query" element in the destination schema is optional, I also included a "Conditional Assignment" functoid to only map the query in case we have columns on the input.
I also used the "Cumulative Count" functoid to count the number of items in the list and used the "Logical Expression" functoid to check if the value is greater than 0:


Based on that result we will create the "Query" element or not, but first we need to prefix the query with the "where" word using the "String Concatenate" functoid:


Simple!

The thing to remember here is to include a "ForEach" in the "Create List"...

Koen