'Selecting sql data with multiple rows and exporting as xml file

I have this JavaScript in my transformer

var dbConn;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection($gc('DataWarehouseXMLDataConfig').sqlDriver,
                                                $gc('DataWarehouseXMLDataConfig').sqlConnString,
                                                $gc('DataWarehouseXMLDataConfig').sqlUsername,
                                                $gc('DataWarehouseXMLDataConfig').sqlPassword);
                                                
var data = dbConn.executeCachedQuery('EXEC [dbo].[GetDataWarehouseData]');
var xml = "";
var xml2 = "";
var row = "";

while(data.next()) {
    row = '<referral>' + '\n' +
        '<careid>' + data.getString('careid') + '</careid>' + '\n' +
        '<patientid>' + data.getString('patientid') + '</patientid>' + '\n' +
        '<dateofreceipt>' + data.getString('dateofreceipt') + '</dateofreceipt>' + '\n' +
        '<datefirstseen>' + data.getString('datefirstseen') + '</datefirstseen>' + '\n' +
        '<nhsnumber>' + data.getString('nhsnumber') + '</nhsnumber>' + '\n' +
        '<hospitalnumber>' + data.getString('hospitalnumber') + '</hospitalnumber>' + '\n' +
        '<surname>' + data.getString('surname') + '</surname>' + '\n' +
        '<forename>' + data.getString('forename') + '</forename>' + '\n' +
        '<dateofbirth>' + data.getString('dateofbirth') + '</dateofbirth>' + '\n' +
        '</referral>';
    xml = xml + row;    
    xml2 = '<?xml version="1.0" encoding="utf-8"?>' + '\n' + '<results>' + xml + '</results>';
}   
channelMap.put('xml2', xml2);
} finally {
if (dbConn) { 
    dbConn.close();
}
}

I'm using this outbound message template

<referral>
<careid></careid>
<patientid></patientid>
<dateofreceipt></dateofreceipt>
<datefirstseen></datefirstseen>
<nhsnumber></nhsnumber>
<hospitalnumber></hospitalnumber>
<surname></surname>
<forename></forename>
<dateofbirth></dateofbirth>
</referral>

I have a File Writer destination and the File Name has a .xml extension. My template in the File Writer is ${XmlUtil.prettyPrint(${message.encodedData})}. The SQL data is being retrieved ok. I don't think I'm a million miles away but I'm doing something fundamentally wrong. What do I need to do to send the required xml file please?

mirth


Solution 1:[1]

I fixed this by adding a mapping step to the source transformer and using JavaScript, like

tmp['referral']['dateofbirth'] = msg['dateofbirth'].toString();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 stonypaul

Related Questions