groovysap-cpi

Groovy script to Convert XML to JSON and format date from dd-mm-yyyy to yyyy-mm-dd

I have the same requirement enter link description here, groovy script gives me expected JSON output. I also have some date fields in XML which i need to format from dd-mm-yyyy to yyyy-mm-dd. I don't know how to format with my groovy script.

def inputXml = message.getBody(String.class);

def parsed = new XmlSlurper().parseText(inputXml);

def values = ['Rows'  : parsed.'**'.findAll { it.name() == 'row' }.collect     { element -> [Values: element.'**'.findAll { !it.childNodes() }*.text()] }]

def fields = ['Columns': parsed.'**'.find { it.name() == 'row' }.collectMany { element -> element.'**'.findAll { !it.childNodes() }*.name()  }]

fields += values;

def outputJson = new groovy.json.JsonBuilder(fields).toPrettyString();

message.setBody(outputJson);
Solution

  • working with below code -

    import java.text.SimpleDateFormat 

def xml = """<?xml version="1.0" encoding="UTF-8" standalone="no"?><ROOT><select_response><row><PNR>30140283</PNR><REINR>GPPR</REINR><START_DATE>2022-07-08</START_DATE><END_DATE>2022-07-09</END_DATE><REASON>Meeting with project partners</REASON><LOCATION>Munich, GERMANY</LOCATION><LAND>DE</LAND><CREATED_ON_ISO>2022-07-07T16:40:35.000Z</CREATED_ON_ISO><CREATED_BY>Alexander Ulbing</CREATED_BY><STATUS>approved</STATUS><LAST_CHANGED_ON_ISO>2022-07-07T16:58:10.000Z</LAST_CHANGED_ON_ISO><IMPORT_DATE>2022-07-07T17:00:07.326467+00:00</IMPORT_DATE><REQUEST_NAME>Business Trip MUC ATV</REQUEST_NAME><START_TIME>11:30</START_TIME><END_TIME>12:30</END_TIME><TEST_1></TEST_1></row><row><PNR>30161871</PNR><REINR>GRRL</REINR><START_DATE>2022-09-18</START_DATE><END_DATE>2022-09-24</END_DATE><REASON>EELS, CL, TEM investigations on carbon GaN and HEMT structures</REASON><LOCATION>Vienna, AUSTRIA</LOCATION><LAND>AT</LAND><CREATED_ON_ISO>2022-07-14T13:24:07.000Z</CREATED_ON_ISO><CREATED_BY>Ze Scales</CREATED_BY><STATUS>approved</STATUS><LAST_CHANGED_ON_ISO>2022-07-14T13:27:01.000Z</LAST_CHANGED_ON_ISO><IMPORT_DATE>2022-07-14T13:30:03.143574+00:00</IMPORT_DATE><REQUEST_NAME>TEM measurement week in Vienna</REQUEST_NAME><START_TIME>09:00</START_TIME><END_TIME>16:00</END_TIME><TEST_1></TEST_1></row><row><PNR>30140257</PNR><REINR>V7YL</REINR><START_DATE>2023-04-26</START_DATE><END_DATE>2023-04-27</END_DATE><REASON>attend conference</REASON><LOCATION>Villach, AUSTRIA</LOCATION><LAND>AT</LAND><CREATED_ON_ISO>2023-02-23T09:25:09.000Z</CREATED_ON_ISO><CREATED_BY>Sybille Ofner</CREATED_BY><STATUS>approved</STATUS><LAST_CHANGED_ON_ISO>2023-02-23T09:25:39.000Z</LAST_CHANGED_ON_ISO><IMPORT_DATE>2023-02-24T04:50:50.183928+00:00</IMPORT_DATE><REQUEST_NAME>POWERcon 2023</REQUEST_NAME><START_TIME>08:30</START_TIME><END_TIME>16:15</END_TIME><TEST_1></TEST_1></row></select_response></ROOT>"""

def parsed = new XmlSlurper().parseText(xml)

parsed.select_response.row.children().findAll { it.name().equals('START_DATE') }.replaceNode {
        'START.DATE'(it.attributes(), it.children(), formatDate(it.text()))
} 
parsed.select_response.row.children().findAll { it.name().equals('END_DATE') }.replaceNode {
        'END.DATE'(it.attributes(), it.children(), formatDate(it.text()))
} 

def modifiedNode  =  new XmlSlurper().parseText(groovy.xml.XmlUtil.serialize(parsed))

def values = ['Rows'  : modifiedNode.'**'.findAll { it.name() == 'row'}.collect { element -> [Values: element.'**'.findAll { !it.childNodes() && it.text()}*.text()]}]

def fields = ['Columns': modifiedNode.'**'.find { it.name() == 'row'}.collectMany { element -> element.'**'.findAll { !it.childNodes() && it.text()}*.name()}]

fields += values

println new groovy.json.JsonBuilder(fields).toPrettyString()

def formatDate(String inputDate){
    SimpleDateFormat formatter = new SimpleDateFormat('yyyy-MM-dd')
    Date parseDate = formatter.parse(inputDate)
    def formattedDate = parseDate.format("dd-MM-yyyy");
    return formattedDate
}