Smook Mediator available in WSO2 MI/EI/ESB can be used to do various message transformations. For an example read
CSV file and transform to XML. I had a requirement where I wanted to load a CSV file content into the database. So, my initial idea was to convert CSV content into XML, then use the XSLT mediator to generate a data service request. But it will be a costly operation with lots of resource consumption.
Therefore, instead of generating a data service request via XSLT, I tried to use the options available in Smooks. Smooks provides FreeMaker based templating support that can be used to convert CSV to SOAP.
Here, first I have created a sample input CSV file and expected Data Service sample request message. With this, it will be very easy to generate data mapping. Below are the examples of CSV and Data Service models
Sample.csv:
UserId,NameFirst,NameLast
1,Chandana,Napagoda
2,John,Tester
SampleOut.xml :
<au:addUsers_batch_req xmlns:au="https://blog.napagoda.com">
<au:NameFirst>Chandana</au:NameFirst>
<au:NameLast>Napagoda</au:NameLast>
<au:System>-1234</au:System>
</au:addUsers>
<au:NameFirst>John</au:NameFirst>
<au:NameLast>Tester</au:NameLast>
<au:System>-1234</au:System>
</au:addUsers>
</au:addUsers_batch_req>
When I created the Smook configuration using WSO2 IDE, the view will be like this
Next, click on the input task, then select input CSV file as input data. After that, define fields in the CSV file, using the Input configuration section. In Addition to above, I have skipped the first line, and each record was marked as User, and parent Element defined as Users.
Next, right-click on the 'Input Task', and mouse hover on the 'Add Task' option and click on the 'Apply Template' option.
You will get a dialogue like below to select the output message type. For me, it is XML.
In the next dialogue, I can select my Output file format(Data Service Request).
Once we select the Smooks Output model and click on the 'Finish' button, Initial data mapping screen will look like below.
Next, select 'User' element from the input model and drop it into 'addUsers' output model. This will generate necessary Freemaker template elements to iterate input rows.
Next map each input element into expected output elements. When ding that, you have to select each leaf element from the input model and drop into the output leaf element.
When navigating to source view of Smooks configuration, below configuration has been auto-generated.
<?xml version="1.0" encoding="UTF-8"?><smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd" xmlns:csv="http://www.milyn.org/xsd/smooks/csv-1.2.xsd" xmlns:ftl="http://www.milyn.org/xsd/smooks/freemarker-1.1.xsd">
<params>
<param name="stream.filter.type">SAX</param>
<param name="inputType">input.csv</param>
<param name="input.csv" type="input.type.actived">File:/E:\test\done\Sample.csv</param>
</params>
<csv:reader fields="UserId,NameFirst,NameLast" recordElementName="User" rootElementName="Users" skipLines="1"/>
<ftl:freemarker applyOnElement="#document">
<ftl:template><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<au:addUsers_batch_req xmlns:au="https://blog.napagoda.com">
<#list .vars["Users"]["User"] as User>
<au:addUsers>
<au:UserId>${.vars["User"]["UserId"]}</au:UserId>
<au:NameFirst>${.vars["User"]["NameFirst"]}</au:NameFirst>
<au:NameLast>${.vars["User"]["NameLast"]}</au:NameLast>
</au:addUsers>
</#list>
</au:addUsers_batch_req>]]></ftl:template>
<param name="modelSrc">File:/E:\test\done\SampleOut.xml</param>
<param name="modelSrcType">XML</param>
<param name="messageType">XML</param>
<param name="templateDataProvider">input</param>
</ftl:freemarker>
<resource-config selector="#document">
<resource>org.milyn.delivery.DomModelCreator</resource>
</resource-config>
</smooks-resource-list>
