BizTalk 2010 Rollup – Sequence to Flat File Mapping


Earlier in the week I spent some time trying to accomplish what I thought was going to be a trivial task – mapping from a sequence structure to a flat file structure using BizTalk Development Tools.

The main idea is to take a looping structure on the left hand side and map it to a flat structure on the right hand side.

As it turns out, it’s pretty easy to do, and not complicated.  I spent a lot of time trying to do something more dramatic, if you’re looking for this info, I think you’ll be surprised by how logical the solution is.

Take a schema like the following:

image

Which defines a structure along the lines of this:

<ns0:Account xmlns:ns0="http://Mapping_Project.Inbound">
  <FirstName>Jed</FirstName>
  <LastName>Bartlett</LastName>
  <Address>
    <Line1>1 Constitution Ave</Line1>
    <Line2></Line2>
    <Suburb>Lincoln</Suburb>
    <State>Nebraska</State>
    <Country>USA</Country>
    <PostCode>90210</PostCode>
    <Type>Home</Type>
  </Address>
  <Address>
    <Line1>Suite 10</Line1>
    <Line2>1090 Eggplant Blvd</Line2>
    <Suburb>Washington</Suburb>
    <State>Nebraska</State>
    <Country>USA</Country>
    <PostCode>90211</PostCode>
    <Type>Work</Type>
  </Address>
</ns0:Account>

We’re going to try and transform it into something ugly, like this:

image

Which means flattening the data from the left hand side to the right hand side – but how?

image

First up, generate some test data. It’s a very good habit to get into, and it’s easy as pie. Right click on a schema and select “Generate Instance”. Modify the values so they’re easy to debug with and you’re set.

image

Let’s look at how we transform this simple XML.  The answer, as it turns out is blindingly simple.

Open up your map..

Now, let’s assume we want to apply a condition to how we separate the data on the left hand side.  Let’s assume there will be two types of addresses (Home and Work).  Our aim is to assign all the “Address 1” address details as “Home”, and all the “Address 2” details as “Work”.

Drop two Logical EQUALs functoids onto the map and join them to the “Type” attribute.  Then, open each functoid and we’ll set the comparison value to ‘Home’ and ‘Work’ respectively (omit the quotes).  This applies the logic to split the output.

1. image 2. image

Now, assign all the attributes directly across to their destination.

image

Next, connect each equals functoid to the destination it reflects, i.e. connect the functoid for “Home” to each of the “Address 1” properties, etc.  Don’t forget to map the “Type” too, if you want to capture that detail.

image

Given the data I’ve specified above – let’s give it a test!  With the map open in Visual Studio, open the Properties Window and take a look at the following details:

image

Change the “Test Map Input” to “XML” and then you can specify the path to a test XML document (which you might have created earlier).  Here’s how mine ended up:

image 
image

To test, right click on the map file in Solution Explorer and select “Test Map”.  You should get some results in the output window:

TestMap used the following file: <file:///C:\Dev\BizTalk\Mapping\Mapping Project\Test Data\Inbound_Data.xml> as input to the map.
Test Map success for map file C:\Dev\BizTalk\Mapping\Mapping Project\Maps\InboundToOutbound.btm. The output is stored in the following file: <file:///C:\Dev\BizTalk\Mapping\Mapping Project\Test Output\Output.xml>
Component invocation succeeded.

Which provides a generated instance which looks like this:

<ns0:AccountDetails xmlns:ns0="http://Mapping_Project.Outbound">
  <Name>Jed Bartlett</Name>
  <Address1_Line1>1 Constitution Ave</Address1_Line1>
  <Address1_Line2 />
  <Address1_Suburb>Lincoln</Address1_Suburb>
  <Address1_State>Nebraska</Address1_State>
  <Address1_Country>USA</Address1_Country>
  <Address1_PostCode>90210</Address1_PostCode>
  <Address1_Type>Home</Address1_Type>
  <Address2_Line1>Suite 10</Address2_Line1>
  <Address2_Line2>1090 Eggplant Blvd</Address2_Line2>
  <Address2_Suburb>Washington</Address2_Suburb>
  <Address2_State>Nebraska</Address2_State>
  <Address2_Country>USA</Address2_Country>
  <Address2_PostCode>90211</Address2_PostCode>
  <Address2_Type>Work</Address2_Type>
</ns0:AccountDetails>

There were some warnings about multiple mappings, but I didn’t find the absence of a looping functoid to be a problem.  I realise this is a pretty obvious and simple mapping, but when I went looking for examples about how to solve this type of mapping, I couldn’t find much at all (including from e-books).

There’ll be more on BizTalk mapping and other fun stuff in some upcoming articles.


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>