Search code examples
xsdwso2freemarkersmookswso2-esb

How to process each line of a CSV to a single XML output - WSO2/Smooks

TL;DR: I have a multline CSV and I want to process line by line and generate a different XML for each line.

I have the following CSV:

Chathuri,Wimalasena,Female,25,SriLanka
Saminda,Wijerathne,Male,26,SriLanka
Dakshitha,Rathnayaka,Female,24,SriLanka
Harsha,Martin,Male,24,SriLanka

Currently I am using smooks and doing the following transformation:

<?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
        <people>
                <person>
                <lastname>Wimalasena</lastname>
                <firstname>Chathuri</firstname>
                <gender>Female</gender>
                <age>25</age>
                <country>SriLanka</country>
            </person>
            <person>
                <lastname>Wijerathne</lastname>
                <firstname>Saminda</firstname>
                <gender>Male</gender>
                <age>26</age>
                <country>SriLanka</country>
            </person>
            <person>
                <lastname>Rathnayaka</lastname>
                <firstname>Dakshitha</firstname>
                <gender>Female</gender>
                <age>24</age>
                <country>SriLanka</country>
            </person>
            <person>
                <lastname>Martin</lastname>
                <firstname>Harsha</firstname>
                <gender>Male</gender>
                <age>24</age>
                <country>SriLanka</country>
            </person>
        </people>
    </soapenv:Body>
</soapenv:Envelope>

The questions is: I want something like this:

<?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
        <people>
            <person>
                <lastname>Wimalasena</lastname>
                <firstname>Chathuri</firstname>
                <gender>Female</gender>
                <age>25</age>
                <country>SriLanka</country>
            </person>
        </people>
    </soapenv:Body>
</soapenv:Envelope>


<?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
        <people>
            <person>
                <lastname>Wijerathne</lastname>
                <firstname>Saminda</firstname>
                <gender>Male</gender>
                <age>26</age>
                <country>SriLanka</country>
            </person>
        </people>
    </soapenv:Body>
</soapenv:Envelope>

My smook.xml:

<?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:/C:\Users\victor.viola\Desktop\test.csv</param>
    <param name="default.serialization.on">true</param>
  </params>
  <csv:reader fields="name,surname,gender,age,country"/>
  <ftl:freemarker applyOnElement="#document">
    <ftl:template><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<people>
    <#list .vars["csv-set"]["csv-record"] as csv_record>
    <person>
        <lastname>${.vars["csv_record"]["surname"]}</lastname>        
        <firstname>${.vars["csv_record"]["name"]}</firstname>        
        <gender>${.vars["csv_record"]["gender"]}</gender>        
        <age>${.vars["csv_record"]["age"]}</age>        
        <country>${.vars["csv_record"]["country"]}</country>        
    </person>
    </#list>    
</people>]]></ftl:template>
    <param name="rootElementName">people</param>
    <param name="modelSrc">File:/C:\Users\victor.viola\Desktop\example.xsd</param>
    <param name="modelSrcType">XSD</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>
Solution

  • With smooks, you can route messages into files or JMS queues.

    Please, have a look at this sample that read the file in streaming mode and split it's content into files or JMS messages (it works in Fixed Length but this is the same with csv, just use csv:reader)

    Proxy service : 

    <?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse" name="VFSProxy" transports="vfs" startOnLoad="true" trace="disable">
    <target>
        <inSequence>
            <property name="DISABLE_SMOOKS_RESULT_PAYLOAD" value="true"/>
            <smooks config-key="SmooksSplitFileFL">
            <!--smooks config-key="SmooksSplitJMSFL"-->
                <input type="text"/>
                <output type="xml"/>
            </smooks>
        </inSequence>
    </target>
    <parameter name="transport.vfs.FileURI">vfs:file:///C:/In</parameter>
    <parameter name="transport.vfs.FileNamePattern">.*.fl</parameter>
    <parameter name="transport.vfs.ContentType">text/plain</parameter>
    <parameter name="transport.vfs.Streaming">true</parameter>
    <parameter name="transport.PollInterval">15</parameter>
    <parameter name="transport.vfs.ActionAfterProcess">DELETE</parameter>
    <parameter name="transport.vfs.ActionAfterFailure">DELETE</parameter>
</proxy>

    Smooks that split into files (a local-entry, firstname is used to name the file so, if there is multiples lines with the same firstname, you will have the last one)

    <?xml version="1.0" encoding="UTF-8"?>
<localEntry xmlns="http://ws.apache.org/ns/synapse" key="SmooksSplitFileFL">
    <smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd" xmlns:ftl="http://www.milyn.org/xsd/smooks/freemarker-1.1.xsd" xmlns:fl="http://www.milyn.org/xsd/smooks/fixed-length-1.3.xsd" xmlns:file="http://www.milyn.org/xsd/smooks/file-routing-1.1.xsd">
        <params>
            <param name="stream.filter.type">SAX</param>
            <param name="default.serialization.on">false</param>
        </params>
        <fl:reader fields="LASTNAME[2],FIRSTNAME[10]" skipLines="0"/>
        <resource-config selector="record">
            <resource>org.milyn.delivery.DomModelCreator</resource>
        </resource-config>
        <file:outputStream openOnElement="record" resourceName="fileSplitStream">
            <file:fileNamePattern>${.vars["record"].LASTNAME}.xml</file:fileNamePattern>
            <file:destinationDirectoryPattern>C:\Processed</file:destinationDirectoryPattern>
            <file:highWaterMark mark="-1"/>
        </file:outputStream>
        <ftl:freemarker applyOnElement="record">
            <ftl:template>/repository/resources/smooks/RecordToXML.ftl</ftl:template>
            <ftl:use>
                <ftl:outputTo outputStreamResource="fileSplitStream"/>
            </ftl:use>
        </ftl:freemarker>
    </smooks-resource-list>
    <description/>
</localEntry>

    Smooks that split into JMS messages 

    <?xml version="1.0" encoding="UTF-8"?>
<localEntry xmlns="http://ws.apache.org/ns/synapse" key="SmooksSplitJMSFL">
    <smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd" xmlns:ftl="http://www.milyn.org/xsd/smooks/freemarker-1.1.xsd" xmlns:fl="http://www.milyn.org/xsd/smooks/fixed-length-1.3.xsd" xmlns:jms="http://www.milyn.org/xsd/smooks/jms-routing-1.2.xsd">
        <params>
            <param name="stream.filter.type">SAX</param>
            <param name="default.serialization.on">false</param>
        </params>
        <fl:reader fields="LASTNAME[2],FIRSTNAME[10]" skipLines="0"/>
        <resource-config selector="record">
            <resource>org.milyn.delivery.DomModelCreator</resource>
        </resource-config>
        <jms:router routeOnElement="record" beanId="Person" destination="dynamicQueues/TestFL">
            <jms:connection factory="QueueConnectionFactory"/>
            <jms:jndi contextFactory="org.apache.activemq.jndi.ActiveMQInitialContextFactory" providerUrl="nio://localhost:61616"/>
            <jms:highWaterMark mark="-1"/>
        </jms:router>
        <ftl:freemarker applyOnElement="record">
            <ftl:template>/repository/resources/smooks/RecordToXML.ftl</ftl:template>
            <ftl:use>
                <ftl:bindTo id="Person"/>
            </ftl:use>
        </ftl:freemarker>
    </smooks-resource-list>
    <description/>
</localEntry>

    Template (create a file ESB_HOME/repository/resources/smooks/RecordToXML.ftl) : 

    <#assign rec = .vars["record"]>
<person>
    <lastname>${rec.LASTNAME}</lastname>
    <firstname>${rec.FIRSTNAME}</firstname>
</person>