CSV to SOAP Message conversion with Smooks Mediator

Smook Mediator available in WSO2 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:addUsers>
<au:UserId>1</au:UserId>
<au:NameFirst>Chandana</au:NameFirst>
<au:NameLast>Napagoda</au:NameLast>
<au:System>-1234</au:System>
</au:addUsers>
<au:addUsers>
<au:UserId>2</au:UserId>
<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>



Comments

Post a Comment

Popular posts from this blog

XSLT - Modify Date and DateTime value

If you want to perform date and time related opertions with XSLT 'date' or 'dateTime' values, you can use 'xs:yearMonthDuration' or 'xs:dayTimeDuration' function to achieve this. You can add or reduce years, months, date, hours or even minutes through those functions.  xs:yearMonthDuration For this function input value is year month duration and it should be passed in  ISO_8601 format n- the [n] is replaced by the value for each of the date and time elements P - The duration designator(for period). nY- the number of years. nM- the number of months. ex: xs:yearMonthDuration('P1Y2M') - 1 Year and 2 Months ex:  xs:yearMonthDuration('-P1Y2M')  - Negative 1 Year and 2 Months xs:dayTimeDuration For this function input value is day time duration and it also needs to be passed in  ISO_8601 format n- the [n] is replaced by the value for each of the date and time elements P - The duration designator(for period). nD- the
Read more

Yield Price Sri Lanka - Android Application

Image
" Yield Price Sri Lanka " is an Android based application which helps users to check Market Price Information using their mobile devices. This application concept is based on person X( will update his name after approvals) and I did slight modifications on the application to support in a low cost infrastructure. Using this " Yield Price Sri Lanka " application, you can check prices of various goods instantly, without going to the market. I am updating price information in Daily & Weekly basis according to the Colombo Manning Market prices. Initial application was developed to support English language only. With current latest version we are supporting English, Tamil and Sinhalese languages. This application will update price information in real time and when describing the application's back-end(server) architecture, it has developed to serve JSON based output with many concurrent requests without blocking any request. Within the mobile Application wh
Read more

Integrate With Mutual Certificate Authentication based Service

There are many ways to secure a Service/API using different Authentication mechanisms. Mutual Certificate authentication(AKA: Client Certificate Authentication) is one of the ways to secure Service or API. Recently I was trying to integrate with an API which is secured using Mutual Certificate Authentication. In this scenario, the backend service team gave me their certificate-based on PKCS#12. When integrating this backend system with WSO2 Platform, we have to use 'Multi-HTTPS transport'. With this 'Multi-HTTPS transport', it will grant great flexibility to define different SSL profiles with separate trust stores and key stores for different IPs or hosts. This custom SSL profile can be implemented for Client to Synapse Runtime(Client --> ESB) or Synapse Runtime to Backend(ESB --> Backend). If it is Client to ESB, then you have to define custom SSL Profiles in the transportReceiver section. In my use-case, it was for ESB to Backend. So I had to add custom SSL prof
Read more