read sqlserver database using mirth connect and convert it into xml format and vice versa

Question

I have a requirement where I have to read data from sql server local database and first map it in XML file provided by another third party org. who have their own database. Then once I have proper mapping of fields I have to transform the data from sql server database to XML format and vice versa. So far, I am able to connect sqlserver database in mirthconnect however I dont know what steps are required to create in channels and transformer to carry the task of reading data and mapping corresponding fields to XML format provided by third party and finally writing in XML file provided and vice versa.

In short if I can get details of creating such channel in mirth connect where I can read sql server database and map the fields in corresponding xml file....I guess I can write to it. Same way applies if I go from xml format to sqlserver database. Can someone tell me how to accomplish this?

For database field mapping whats the best way to map fields entirely on two different databases is there any tool which can help....

Also once the task of transforming the data from one end to another is accomplished is there any way of validation in mirth connect that verifies that data is correctly moved from one to another?

Answer 1

If you want to process one row at a time, the normal database reader will work fine; just set the data type under Summary to XML for all steps. Set a destination of channel writer to nowhere and run it once to see what it does in the Dashboard. You can copy and paste that as an example into your message template so you can map variables.

If you want to work an entire result at one time in the Transformer steps, I find it easier to create a custom reader and use "FOR XML RAW, ELEMENTS" on the end of my Microsoft SQL query. Something like:

    //build connection
    var dbConn =      DatabaseConnectionFactory.createDatabaseConnection('com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://servername:1433;databaseName=dbname;integratedSecurity=true;','','');  //this uses the MS JDBC driver and auth dll
    //query results with XML output from server  'FOR XML' statement at end
    var result = dbConn.executeCachedQuery("SELECT col1 AS FirstColumn, col2 AS SecondColumn FROM [dbname].[dbo].[table1] WHERE [processed] = 'False' FOR XML RAW, ELEMENTS");

    //Make sure we are at the top of results
    result.beforeFirst();

    //wrap XML.  Namespace etc. not required
    XMLresult = '<message>';

    //XML broke up across several rows in one column.  Re-combine
    while (result.next()) {

    XMLresult += result.getString(1);

    }

    XMLresult += '</message>';

    dbConn.close();

    return XMLresult;