Introduction:


This blog describes the configuration steps for converting the FlatFile to XML with ‘n’ nested levels using custom Adapter Module ContentConversionBean ( Direction -> Sender ).


Pre-Requisites:

  For novice users, Please read the link provided in help.sap.com related to File Content Conversion

Java Version:

This adapter module is written using JDK 1.5 API’s, so before deploying the Adapter Module (.ear file) make sure the JDK version 1.5 and above.


Problem:


Many times, we get requirements to convert the Flat File into XML with more than 3 nested levels. Using Standard Adapter/Adapter Module we will be able to achieve the above requirement with limitation up to 4 levels only. If the requirement is to generate the XML with more than 4 nested levels we can achieve this using Java Mapping. The problem here is that the java mapping is confined only to that particular interface, and hence it cannot be re-used for other scenarios.


Solution:


I have created a Generic Adapter Module to address the above problem for  nested/deep hierarchies of generating XML.

This module uses the most of the standard FCC parameters and the use of the those parameters are listed in the Table2.

Table 1:

The following properties should be configured only in the module configuration of the channel.

Property Name Required/Optional Description
Direction Required If the module used in sender channel provide the value as sender and if it is used in receiver channel specify receiver
Configuration_Mode Optional

Configuration_Mode contains either file or local as the value.

  • If  Configuration_Mode = local, then all the Table 2 properties should be specified in the Module Configuration of the Channel.
  • If Configuration_Mode = file, then use the File_Path property name to specify the file path of the properties which is stored in the NFS file system of SAP PI
File_Path Optional

1) If Configuration_Mode = local, then this property will not be applicable.

2) If Configuration_Mode = file, then the properties which is specified in the Table 2 should be specified in the properties file and provide the absolute path of the properties file.

  Note: Properties file should be stored in the NFS file system of SAP PI.

Validate_XSD Optional

This property is similar to the Validation By Adapter, but the functionality is achieved through Adapter Module.

Specify the absolute path of the XSD file which is stored in the NFS file system of SAP PI.

  Note : This property will be used only in case of sender channel.


Table 2:


The following properties can be specified either in the file or in the Module Configuration of the channel depends on the value specified in the Configuration_Mode property specified in the Table1.

Property Name Required/Optional Description
DocumentName Required The value of the property specifies the root element of the XML document.
DocumentNamespace Optional If values is specified for this property, namespace is added to the root element i.e.,DocumentName is namespace qualified.
DocumentOffset Optional

The value of this property, specifies the number of lines needs to be skipped at the beginning of the document.

This enables you to skip comment lines or column names during processing

RecordSetName.recordSetStructure Optional

Specify the recordset names and the cardinality of substructures as follows:<NameA,nA,NameB,nB,…> , where nA=1,2,3,… or* (for a variable, unlimited number, including 0)

Here NameA, NameB,… is the recordset Names

  nA specifies the cardinality.

RecordSetSequence Optional

This property either contains Ascending or Variable. If not configured, then Ascending will be considered as default.


Ascending :

The sequence of the recordset structures is assumed to be unique. A new recordset is started as soon as an earlier structure occurs.


Variable :

  The sequence of the recordset structures is assumed not to be fixed. A new recordset is not started until another structure occurs that is defined with a fixed number. If all structures are defined as variable, the system interprets the entire document as a single recordset.

KeyFieldName Optional

If you have entered a variable number of substructures under Recordset Structure, that is, you have entered the value * for at least one structure, specify a Key Field Name.

  The parser identifies the substructures by their content. This happens using the key field with different constants for the substructures. In this case, you must specify a key field, and the field name must occur in all substructures.

RecordSetName.fieldNames Required Specify the fieldNames separater by comma (,) which belongs to the corresponding recordSet structure
RecordSetName.beginSeparator Optional

If you want to define an additional character string as a separator before the first column in a row, make a specification here.

  The system skips this separator when it processes this column (otherwise the system would treat it as part of the first column).

RecordSetName.endSeparator Optional

If you want to define an additional string as a separator after the last column in a row, specify it here.

The system skips this separator when it processes the last column (otherwise the system would treat it as part of the last column).

  Note: if fieldNames property is defined for a recordSet, then endSeparator is required for that corresponding recordSet.

RecordSetName.fieldSeparator Optional

If you make an entry here, the Module expects that the structure contains the specified character string (one or more characters) as a separator between the individual columns.

If you have not made an entry for fieldFixedLengths , this is the only specification to identify the individual columns in a row.

RecordSetName.fieldFixedLengths Optional

If you make an entry here, the Module expects a character string that contains the lengths of the structure columns as arguments separated by commas.

If you also specify a separator for the columns, you must not add its length to the length of the columns.

  This entry is mandatory if you have not made an entry for RecordSetName.fieldSeparator

RecordSetName.enclosureSign Optional

Specify a string that acts as a text delimiter. Text enclosed by such delimiters is transferred to the target structure unchanged, although the default setting is to remove all text delimiters. Separators within such texts are ignored.

This parameter is optional. The default setting is an empty value (no text delimiter).

  Note : This property will be used only when RecordSetName.fieldSeparator is used.

RecordSetName.enclosureSignEnd Optional

If the text delimiters for the beginning and end of the text are different, specify the text delimiter for the end of the text here.

If you do not make an entry here, the entry from NameA.enclosureSign is used.

  Note : This property will be used only when RecordSetName.fieldSeparator is used.

RecordSetName.enclosureSignEscape Optional

Specify a string that replaces the text delimiter if it occurs within a text that it delimits.

When the text is transferred the string is replaced by the value specified in NameA.enclosureSign

  Note : This property will be used only when RecordSetName.fieldSeparator is used.

RecordSetName.enclosureSignEndEscape Optional

Specify a string that replaces the text delimiter for the end of the text if it occurs within a text that it delimits.

When the text is transferred the string is replaced by the value specified in NameA.enclosureSignEnd .

  Note : This property will be used only when RecordSetName.fieldSeparator is used.

RecordSetName.enclosureConversion Optional

Specify YES if the text delimiter is to be removed or if the escape character is to be replaced when the files are transferred. This is the default value.

Enter NO if the character is to be transferred unchanged.

If you specify xml.enclosureSign=” and xml.enclosureSignEsc=”” , text enclosed in quotation marks is transferred unchanged and the quotation marks are removed.

If the escape character for a quotation mark (“” ) occurs in the text itself, it is replaced by the quotation mark during the transfer.

  Note : This property will be used only when RecordSetName.fieldSeparator is used.

RecordSetName.keyFieldInStructure Optional

If the key field of the substructure is to be added to the XML document, enter add . This is the default.

If the key field is to be ignored, enter ignore .

RecordSetName.missingLastFields Optional
  • If the inbound structure has less fields than specified in the configuration then the XML outbound structure is created as follows:
  • ignore –> Outbound structure only contains the fields in the inbound structure
  • add –> Outbound structure contains all fields from the configuration; the fields missing in the inbound structure are empty.
  •   error –> Conversion is terminated due to the incomplete inbound structure. An error message is displayed.
RecordSetName.additionalLastFields Optional
  • If the inbound structure has more fields than specified in the configuration then the XML outbound structure is created as follows:
  • ignore –> Outbound structure only contains the fields in the inbound structure
  • error –> Conversion is terminated due to the incomplete inbound structure. An error message is displayed.
RecordsetName.fieldContentFormatting Optional
  • Enter trim to remove all the leading and trailing spaces. This is the default.
  • Enter nothing to ensure that the value remains unchanged
RecordSetName.fieldNamesMandatory Optional

Acceptable values are yes or no.

Note : This is a custom property and the use case is provided below.


How to create the Properties file:

  1.   Open any text editor
  2.   Save it with extension .properties
  3.   Each and every property occurs in combination of key and value.
    Eg: propertyname=propertyvalue

Comparison of XML Terminology & FCC Terminology with respect to FCC Configuration:

  • XML Document can have only one root element which is parent of all the tags.
    In FCC terminology, the name of the root element needs to be specified in the DocumentName property.
  • In XML terminology, if a Tag contains child tag then the parent tag is termed as Segment.
    In FCC terminology, segment is nothing but recordset.
  • In XML terminology, if a Tag does not contains any child tag, then it is termed as field.
    In FCC terminology, field remain the same ie.field itself.

Sample XML Example:

Sample.jpg

  1. In the above xml, Message is the root element which needs to be specified in FCC as DocumentName=Message
  2. In the above XML, Message Tag contains 2 fields, in FCC it needs to be specified as root.fieldNames=Field1,Field2
  3. In the above XML, Message Tag contains 2 segments namely Details1, Details2, in FCC it needs to be specified as root.recordsetStructure=Details1,1,Details2,1
    Here recordsetStructure property is used to specify the list of segments and their corresponding cardinality should appear under the root element.
  4. Since the fieldNames property is configured for the root element, either the fieldSeparator property or fieldFixedLengths property needs to be specified and also endSeparator needs to be specified.
    eg: root.fieldSeparator = ,
    root.fieldFixedLengths=5,4
    root.endSepartor=’nl’( here ‘nl’ specifies the end line)
  5. In the above XML, Details1 recordset/segment contains the fields which needs to be specified in FCC as root.Details1.fieldNames=Field3, Field4.
  6. Since the fieldNames property is configured for the root.Details1 either the fieldSeparator property or fieldFixedLengths property needs to be specified and also endSeparator needs to be specified.
    eg: root.Details1.fieldSeparator = ,
    root.Details1.fieldFixedLengths=5,4
    root.Details1.endSepartor=’nl’( here ‘nl’ specifies the end line)
  7. Points 5,6 applies for the Details2 recordset/segment as well.
  8. In the above XML, If Details1 or Details2 further contains segments then it needs to be specified in FCC as root.Details1.recordsetStructure=Details3,* ( * specifies 0 or more )  or root.Details2.recordsetStructure=Details4,2  and so on you can build nested hierarchies.

   


Scenario 1:

The below snapshot depicts the xsd which is created with 5 levels of depth

/wp-content/uploads/2016/03/1_901771.jpg

Module Configuration:

In the below configuration, Configuration_Mode is specified as file, we have specified all the FCC properties in the file and stored in the NFS path and specified the path of the file to File_Path property.

The advantage here is, user no need to modify/change the channels in order to change the FCC property.

If the Configuration_Mode is either not specified or configured as local, then all the FCC properties should be configured in the Module Configuration.


/wp-content/uploads/2016/03/2_901787.jpg


  The FCC for the above structure is provided in the properties file as given below.

/wp-content/uploads/2016/03/3_902440.jpg


Illustration of FCC configuration in the properties file:

  • DocumentName = MessageType
  • KeyFieldName=key,specifies the key field name which is available in the first column of each recordSet, Module will read each and every line based on the end separator and needs to identify to which recordset the line should be associated.
  • root.fieldNames=key, Name, Value
  • root.recordsetStructure=Records,*
  • root.Records.fieldNames=key, R_Name, R_Value
  • root.Records.recordsetStructure =RHeader,1,RItem,*,Details,2
  • root.Records.RHeader.fieldNames=key,RH_Name,RH_Value specifies the field names which is created under the RHeader element. To identify any line belongs to RHeader use the keyFieldValue property as root.Records.RHeader.keyFieldValue=3
  • root.Records.RItem.fieldNames=key,RI_Name,RI_Value specifies the field names which is created under the RItem element. To identify any line belongs to RItem use the keyFieldValue property as root.Records.RItem.keyFieldValue=4
  • root.Records.Details.fieldNames= key,D_Name,D_Value specifies the field names which is created under the Details element. To identify any line belongs to Details use the keyFieldValue property as root.Records.Details.keyFieldValue=4
  • root.Records.Details.recordsetStructure=DHeader,1 specifies the recordset name DHeader should appear exactly once.

  root.Records.Details.DHeader.fieldName= key,DH_Name,DH_Value

specifies the field names which is created under the DHeader element. To identify any line belongs to DHeader use the keyFieldValue property as root.Records.Details.DHeader.keyFieldValue=6

Input Data:

/wp-content/uploads/2016/03/4_901789.jpg


Output XML :

/wp-content/uploads/2016/03/5_901799.jpg

Scenario : 2


Requirement:  consider the below requirement, where keyFieldValue is not configured for one of the recordset

  • if the line starts with value HDR, then the line is associated to Header recordset
  • if the line starts with value TRL, then the line is associated to Trailer recordset
  • if the line starts neither with HDR nor with TRL then it has to be associated to Details recordset


  The above requirement cannot be achieved using the Standard FCC and module addressed the above problem, by not configuring the keyFieldValue property    to that corresponding recordset where fieldNames property is configured.


  Note: if keyFieldValue property is not configured for 2 recordsets, for which fieldNames property is configured, then error will be raised, because module will  be unable to determine to which recordset the line should be associated.

    All the FCC parameters are placed in the properties file:


FCC Configuration:

/wp-content/uploads/2016/03/6_902513.jpg


Input File:


  /wp-content/uploads/2016/03/7_901805.jpg


Output XML:

/wp-content/uploads/2016/03/8_901807.jpg

Scenario : 3

The below scenario illustrates how to use the missingLastFields , additionalLastFields and enclosureSign properties.

FCC properties is provided in the properties file for ease of use, as channel is not required to be activated if any of the FCC properties gets changed.

FCC Configuration:

/wp-content/uploads/2016/03/9_902514.jpg

Input File:

/wp-content/uploads/2016/03/10_901812.jpg

Output XML:

/wp-content/uploads/2016/03/11_901813.jpg


Scenario :4

As per the Standard FCC documentation, KeyFieldName is required, if any of the Recordset cardinality is configured as *. If KeyFieldName is not configured then the cardinality should not be configured as * for any of the recordset.

Note : If the KeyFieldName is not configured and if the keyFieldValue is specified for each of the recordset, then keyFieldValue will not be considered while building the XML.

For the sake of simplicity, the FCC is created in the properties file.

 
In the below FCC configuration, KeyFieldName property is not configured.


FCC Configuration:

/wp-content/uploads/2016/03/13_901826.jpg


Input File:

/wp-content/uploads/2016/03/14_901827.jpg

Output XML:


/wp-content/uploads/2016/03/15_901828.jpg

Scenario 5:

Hope you all know that, in PI Data Type, Message Type can be created from scratch or it can be imported as an external definition.

Consider the scenario, where the Message Type XSD is provided by the third party.

Message Type:

/wp-content/uploads/2016/03/16_901831.jpg


FCC Configuration:

/wp-content/uploads/2016/03/17_901836.jpg

Input File:

/wp-content/uploads/2016/03/18_901837.jpg


Output XML:

/wp-content/uploads/2016/03/19_901841.jpg


Description : In the above XML, fields Rec_Type, Fld1,Fld2,Fld3 is missing in the first LineItem, as the corresponding line is not available in the FlatFile.txt. This is the default behaviour.

If the default behaviour is not desired, then use the custom property fieldNamesMandatory as root.LineItems.fieldNamesMandatory=yes . If this property is configured and if we run with above FlatFile.txt then an error will be thrown by the Adapter Module.



Note:

  • In General, if the Recordset( in our eg: LineItems) configured with both the fieldNames property and recordsetStructure property, then fieldNames property considered as optional. i.e.,. if the line belongs to fieldNames ( eg: root.LineItems.fieldNames) is available in the flatfile, the fields configured in the fieldNames property will be generated, else it will not be generated.
  • The custom property fieldNamesMandatory is effective, if the recordset is configured with both the fieldNames property and the recordsetStructure property.

        /wp-content/uploads/2016/03/20_901842.jpg     

        In the above FCC, root.LineItems recordset is configured with both the root.LineItems.fieldNames property and 

      root.LineItems.fieldNames property and root.LineItems.recordsetStructure property and root.LineItems.recordsetStructure            property  and hence the root.LineItems.fieldNamesMandatory=yes is used. 

  • If either the fieldNames property or recordsetStructure propery is not configured then fieldNamesMandatory property will not be taken into consideration.

        /wp-content/uploads/2016/03/21_901843.jpg

          In the above FCC, root.LineItems.Details recordset is configured with root.LineItems.Details.fieldNames property but not

        configured root.LineItems.Details.recordsetStructure property and hence the custom propery

        root.LineItems.Details.fieldNamesMandatory=yes will not be effective.

     

Use the below link which explains conversion of XML with Nested Structures to FlatFile,

Content Conversion for ‘n’ Nested Structures( XML -> Flat File) Using Adapter Module – Receiver

Disclaimer:

  • This Module is optimized to process messages with an approximate payload size <= 5 MB.
  • This Module will not work with messages having attachments
To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Andrew Harvie

    Very nice write up Vignesh, do you share your custom adapter source code?  Multi record sets and deeply nested files are issues we still deal.

    Thanks,
    Andrew

    (0) 
  2. Srikanth Mavuri

    Hi Team,

     

    Could you please help us to provide the Module Code for handling Nested structures. we have same requirement in our project.

    Thanks in Advance.

    Regards,

    Srikanth Mavuri,

    (0) 

Leave a Reply