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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<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:
|
1 2 3 4 |
<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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<?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:
|
1 2 3 4 5 6 7 8 9 |
<?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:
|
1 2 3 |
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
