File Content Conversion for Multi Hierarchical Structure
The need for this approach :
File content conversion on the receiver side is mostly used for data with a header and item level (and some cases sub-item) data hierarchy. The limitation faced here is that content conversion can be used only with XML structures that have a single hierarchy level.
- This weblog is aimed at putting forth a scalable solution for this limitation. Lots of approaches using XML tools etc have already been suggested. This one aims to do it within the standard XI development tools.
Sample Scenario with Multiple Levels of data for a flat File:
Let us consider a case in the below shown structure is the data format in the target side.
And the required file is of the format shown below.
The general approach here is that level 1 can be viewed as header and Fields in that level are the header fields and Level 2 is item and so on. The requirement is that we would have to show the header fields in one line and then its corresponding items in each of the following lines with the corresponding sublevels following the items. Basically a running list of header and its items, subitems and then the next header and so on.
This cannot be directly achieved as the file content conversion will not allow us to use more than one level in its content conversion. The following steps need to be done in order to be able to convert this structure into a flat file.
Whatever the target structure is create another data type as a copy of this structure and add another field of type string in the end and at just one level below the root node as shown below for the example we are discussing.
Note that this data type contains all the fields as required and then one field labelled “Result” in the end and just one level below the route node.
The above type will act as the intermediate structure. Declare another structure with just a node and an element within it as shown below. This shall be the final structure that would be used for the Content Conversion.
Creating a message mapping between the actual target structure shown and the intermediate structure (with the extra field in the end) shown above.
This mapping requires declaration of 2 global variables as shown below.
Except the first field in each level, map the others to constants, meaning field 2 till field “n” in each level must be mapped to constants, for the first field in each level map them to the source as shown in the below example for level 1.
The source field 1 and field 2 are from level 1 and the target field 1 is also from level 1. The user defined function “gen_flt1” would contain the following code.
The following needs to be noted about this Function
- It is a simple user defined function and hence accepts a string and returns a string
- The number of parameters it accepts will be equal to the number of fields in each level. If Level 1 has 4 fields, then all the four fields will be taken as input parameters and then the string “res” would be concatenation of all the four fields separated by space.
- This function would append the records to the global array declared earlier. It exploits the fact that simple mapping functions are executed in the order of the XML and hence we can capture the same order required for the file.
Repeat the same procedure for all the levels. The same function can be re used if the number of fields in each level are the same, else use the same logic and create new function for every level.
After completing the mapping for all the levels, the mapping for the last field “Result” is done as shown below.
Note that the result field does not map to any source fields, instead it is mapped to another user defined function “Populate_Result” . This function would contain the below shown code.
The following needs to be noted about this Function
- It is a context function and does not take any input parameters
- The function reads the Global Array populated earlier and creates new instances of the field “Result” in the target
Once the mapping is completed, check is as shown below to get the desired result.
Create another mapping between the target structure above and the final structure if the flat file.
The mapping only involves 2 simple steps shown below
This would ensure that the number of result segments is transferred into “Data” nodes with in the target as shown below
Create an interface mapping with the between the source and target interfaces and the mapping created in step2 and step3 would be used together as shown below
The mapping can be checked as shown below.
The Target structure thus achieved is same as the one recommended to be used in file content conversion and hence the Content Conversion is now direct. Even though the target has “n” number of fields in each line, since they are concatenated, the field list in the conversion parameters would contain just one field “Result”.
- The Underlying logic is that that number of “Result” elements in the intermediate mapping must be the number of lines in the flat file and in the same order
- Each level needs to be mapped once to be added to the Global Array.
- The number of parameters that the function takes is equal to the number of fields in each level and concatenation must happen likewise.
This logic can be used to convert any hierarchical structure with any number of levels and elements in each level to a flat file by using the standard development tools in XI. The logic also takes care of differences in occurrence between each of sub levels that usually complicate the mapping since Node Functions would have to be used. The content conversion which is very complex to handle even with one hierarchy would now be simple as all the data is in just one field. Any of the separators can be used and file can be generated as fixed length also. Newer fields added to the target data also can be accommodated in the mapping. File Formats with variable alignment based on the hierarchy as shown below can also be achieved, but just concatenating more tab spaces in the UDF “Generate_Flt1”.