BizTalk Services Mapper – WCF SQL Select Query – Cumulative Concatenate

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:

The Destination XML:

First, how could we do this within XSLT?

This can be done with the following XSLT:

The result of the above will be:

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:

Simple!

The thing to remember here is to include a “ForEach” in the “Create List”…

Koen



Working at i8c

i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).

Quality Assurance

i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects.

i8c - ISO9001-2015