XML to Flat file transformations
In this microlearning, we will guide you through the process of transforming XML data into a flat file format, which can then be sent to external systems. We will cover both simple and complex scenarios to help you understand the various steps involved.
Should you have any questions, please contact academy@emagiz.com.
1. Prerequisites
- Basic knowledge of the eMagiz platform
2. Key concepts
This microlearning discusses the approach to transform an XML to a Flat File. Once transformed, that flat file can be used by a target system for further consumption.
As the name suggest this standard component gives you, the user, the possibility to transform an XML message to a flat-file (text, csv or EDIFACT). In the following chapters we will work out two examples. The first example will always be the simpler case, which you will encounter more often. The second example will be the advanced case.
3. XML to Flat file transformation
3.1 Simple scenario step by step
Follow these steps carefully in order to acquire the desired result. If a step is unclear or you are not able to follow it, please contact eMagiz Academy.
Setting up the xml
It is beneficial, when working with this component to prepare your XML message to make this transformation much easier. This can be done to create a structure in which you have a root under which you have one list of records containing all relevant information (i.e Records/Record structure)
Validate before transforming to flat-file
A best practice is to validate the system message before transforming to your flat-file solution.
Adding the correct component
The first step is adding the correct component to your flow. In this case you will need a blue rectangular shape component of the transformer family. The component is called: xml to flat file transformer. It is wise to name it according to its function. This way it is clear for all to see what the function of the component is within the context of the flow. See below for an example of such naming convention.
Select the type of flat file you want to generate
In most cases you will want to generate a csv file. So you can select that option and already state how many columns you want in your output. This number should correspond to the number of attributes in your input xml. After you have executing both actions you only need to press generate columns.
Input
Output
Definining the xpath to create the lines
The first thing we need to set up within the component is the xpath expression we are using to split the incoming xml messages to separate lines we are transforming to lines within the flat file. Standard we often see an xpath like: /Records/Record. In the example below we choose /_:Employees/_:Employee. Alternatively you can determine the namespace prefix and the namespace itself, just as you can in the support object called Xpath expression.
Filling in the correct xpath expression per column
For each column you have just generated you now have to define the xpath expression. Because you were smart in step 1 of this how-to this step just has become a lot easier. Due to the nature of the xml message that will form the input the xpaths you need to set will mimic the name of the attribute they correspond with. See below for an example
Setting the delimiter (optional)
If the delimiter in your output file deviates from the standard (comma) you can specify this under the Advanced tab. The option is called Delimiter. In this field you could place a semicolon, inverted question marks or exclamation marks.
Setting the Line separator (optional)
On the Advanced tab you also have the determine the line separator. Most often this will come into use when working with different operating systems. The default “Linux” enter (\u000A) differs from the default “Windows” enter (\u000D\u000A). So when the customer asks you to use an CarriageReturn and LineFeed as Line separator go for the Windows option.
Setting the charset (optional)
In some cases you need to deviate from the standard UTF-8 charset when formatting the lines. If this is the case you can determine the charset in this component in order for the connected system to handle the input correctly. Examples of different charsets are:
• windows-1252
• US-ASCII
• x-UTF-8-BOM
3.2 Complex scenario step by step
Follow these steps carefully in order to acquire the desired result. If a step is unclear or you are not able to follow it, please contact CAPE Academy.
Setting up the xml
It is beneficial, when working with this component to prepare your xml message to make this transformation much easier. In this complex case this will be done by using a custom xslt to already format the xml in such a way that it will become easier to split and use this component. In the custom xslt you can create a record in which you define two parts of the line that have a certain structure (i.e recordtype and recordvalue).
If you need held setting up such an xml please feel free to contact the CAPE Academy or the eMagiz Competence Center.
Adding the correct component
The first step is adding the correct component to your flow. In this case you will need a blue rectangular shape component of the transformer family. The component is called: xml to flat file transformer. It is wise to name it according to its function. This way it is clear for all to see what the function of the component is within the context of the flow. See below for an example of such naming convention.
Select the type of flat file you want to generate
In these cases you will want to opt for the fixed length format option and in this example we need two columns.
Input
Output
Definining the XPath to create the lines
The first thing we need to set up within the component is the xpath expression we are using to split the incoming xml messages to separate lines we are transforming to lines within the flat file. The expression is completly depended on how you have built your xml in step 1
Filling in the correct xpath expression per column
For each column you have just generated you now have to define the xpath expression. Because you were smart in step 1 of this how-to this step just has become a lot easier. Due to the nature of the xml message that will form the input the xpaths you need to set will mimic the name of the attribute or entity they correspond with. See below for an example
Setting the Line separator (optional)
On the Advanced tab you also have the determine the line separator. Most often this will come into use when working with different operating systems. The default “Linux” enter (\u000A) differs from the default “Windows” enter (\u000D\u000A). So when the customer asks you to use an CarriageReturn and LineFeed as Line separator go for the Windows option.
Setting the charset (optional)
In some cases you need to deviate from the standard UTF-8 charset when formatting the lines. If this is the case you can determine the charset in this component in order for the connected system to handle the input correctly. Examples of different charsets are:
• windows-1252
• US-ASCII
• x-UTF-8-BOM
4. Key takeaways
Key considerations when transforming XML to a flat-file are:
- Create a simplified structure in your system message before transforming to flat-file (i.e., Records/Record).
- Do not define a namespace for your system message to make life easier.
- Consider which character encoding is required by the external system and take additional measures if this deviates from the standard (i.e., UTF-8).
5. Suggested Additional Readings
If you are interested in this topic and want more information on it please read the help text provided by eMagiz and read the following links: