Tuesday, December 31, 2013

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">

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

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"
                exclude-result-prefixes="msxsl s0">
  <xsl:output method="xml"

  <xsl:template match="/">
    <xsl:apply-templates select="/s0:GetProducts" />
  <xsl:template match="/s0:GetProducts">
      <xsl:if test="Column">
          <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>

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">
    where Name_0='Value_0'
    and Name_1='Value_1'
    and Name_2='Value_2'

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:

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:


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


No comments:

Post a Comment