Map a Simple Row Set to a Parent Child structure

Recently a colleague asked me to solve the following problem;

“ I have an xml file that has a simple row set. I need to restructure the data into a parent child relationship. A parent record would have the Type element equal to ‘P’ and the child type would be ‘C’. Here’s an example of what it would look like.

</rowset>

I want to restructure it like this below.

</row>

</row>

</rowset>

“

I decided to blog about it how I solved it here because I think the solution is not trivial. Similar problems and solutions have been described at http://eaiteamblog.blogspot.com/2009/07/mapping-unrelated-records.html and http://blogs.msdn.com/chrisromp/archive/2008/07/31/muenchian-grouping-and-sorting-in-biztalk-maps.aspx . Neither fitted the problem here.

My solution uses two global variables (startOfParent and isEndOfParent) and two inputs of the same message to the map. The map is shown below. The first scripting functoid declares the global variables as shown below.

///*Declare the global variable before any other functions
//Set startOfParent to the first record
//Set it to total record count to begin with.*/

int startOfParent = 1 ;
bool isEndOfParent;

public void DeclareVariables (){}

The second scripting functoid increments the startOfParent and sets isEndOfParent to true when a new parent is found. THis is shown below;

///*Update startOfParent global variable. A new parent has been found
// If value of type in next row is P
// and it is not endofParent
// and index > startOfRecord
// else leave it as is.*/

public int updatenext_P_Type(bool isNextPType, int index, int totalRecords)
{
    if (isNextPType == true && isEndOfParent == false && index > startOfParent )
                {
                   isEndOfParent = true;

startOfParent = index;

}

              //Reset at end of inner loop
               if (totalRecords == index)
               {
                      isEndOfParent = false;
                }
                return startOfParent;
}

The third scripting functoid checks whether the end of current parent has been reached in the inner loop.

///*Get isEndOfParent
//
//.*/

public bool getisEndofParent()
{
return isEndOfParent;
}

The condition on the top loop checks whether the type is equal to P. The conditions on the bottom loop check that type is equal to C, the index of second loop is greater than the index of the first loop, index of first loop is less than or equal to the start of the parent and that we have past the end of the parent being parsed.