Sort XML child nodes by date field in Groovy Script

Question

My XML is like below... I need to sort EmpJob nodes under jobInfoNav by startDate element. I tried below codes and followed below logs, it doesn't give any error but list is not sorting. Please help.

I am using this groovy script in SAP CPI.

XML:

<PerPerson>
<PerPerson>
  <employmentNav>
    <EmpEmployment>
      <jobInfoNav>
        <EmpJob>
          <event>10193</event>
          <userId>abc123</userId>
          <eventReason>SP</eventReason>
          <startDate>2020-09-20T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10196</event>
          <userId>abc123</userId>
          <eventReason>RP</eventReason>
          <startDate>2019-04-14T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10186</event>
          <userId>abc123</userId>
          <eventReason>RL</eventReason>
          <startDate>2019-08-19T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10203</event>
          <userId>abc123</userId>
          <eventReason>HN</eventReason>
          <startDate>2018-05-02T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10193</event>
          <userId>abc123</userId>
          <eventReason>ZC</eventReason>
          <startDate>2020-03-08T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10193</event>
          <userId>abc123</userId>
          <eventReason>EC</eventReason>
          <startDate>2018-09-23T00:00:00.000</startDate>
        </EmpJob>
      </jobInfoNav>
    </EmpEmployment>
  </employmentNav>
</PerPerson>
<PerPerson>
  <employmentNav>
    <EmpEmployment>
      <jobInfoNav>
        <EmpJob>
          <event>10203</event>
          <userId>abc789</userId>
          <eventReason>HN</eventReason>
          <startDate>2020-06-22T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10193</event>
          <userId>abc789</userId>
          <eventReason>ZC</eventReason>
          <startDate>2020-06-22T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10180</event>
          <userId>abc789</userId>
          <eventReason>LD</eventReason>
          <startDate>2020-06-24T00:00:00.000</startDate>
        </EmpJob>
      </jobInfoNav>
    </EmpEmployment>
  </employmentNav>
</PerPerson>
<PerPerson>
  <employmentNav>
    <EmpEmployment>
      <jobInfoNav>
        <EmpJob>
          <event>10193</event>
          <userId>abc999</userId>
          <eventReason>SP</eventReason>
          <startDate>2020-08-30T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10206</event>
          <userId>abc999</userId>
          <eventReason>RN</eventReason>
          <startDate>2020-08-24T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10203</event>
          <userId>abc999</userId>
          <eventReason>HC</eventReason>
          <startDate>2020-01-21T00:00:00.000</startDate>
        </EmpJob>
      </jobInfoNav>
    </EmpEmployment>
  </employmentNav>

Groovy script:

   def xml =  new XmlSlurper().parseText(body)     //use of of these depending on which code you are using
   def xml =  new XmlParser().parseText(body)   //use of of these depending on which code you are using

     XmlUtil.serialize(new StreamingMarkupBuilder().bind {                                           
PerPerson{
    PerPerson {
        employmentNav{
            EmpEmployment{
                jobInfoNav{
                     xml.jobInfoNav[0]
                     .children()
                     .sort { Date.parse("yyyy-MM-dd'T'HH:mm:ss.sss", it.startDate.text()) }
                     .each { e ->
                         EmpJob {
                                event(e.event.text())
                                userId(e.userId.text())
                                eventReason(e.eventReason.text())
                                startDate(e.startDate.text())
                                }     
                                        
                            }
                        }
   
                     }
                 }
             }
         }
    }
    )
 
    message.setBody(XmlUtil.serialize(xml))

also tried below

// xml.PerPerson.employmentNav.EmpEmployment.jobInfoNav.EmpJob.sort(true) {it.attribute('startDate')}

also tried below code

 new StreamingMarkupBuilder().bind {                                                                    // not working
    xml.PerPerson.PerPerson.EmpEmploymentNav.EmpEmployment.jobInfoNav[0].children().sort { Date.parse("yyyy-MM-dd'T'HH:mm:ss.sss", it.startDate.text()) }
                     .each { e ->
                         EmpJob {
                                event(e.event.text())
                                userId(e.userId.text())
                                eventReason(e.eventReason.text())
                                startDate(e.startDate.text())
                                }
                            }
                        }

I don't see any errors but EmpJob nodes not sorted. Please help.

followed online threads

https://groups.google.com/forum/#!topic/groovy-user/rNapYFuFl-0

https://community.smartbear.com/t5/API-Functional-Security-Testing/Sorting-XML-response-before-assertion/td-p/107186

How to sort XML with Groovy using value of Node (Date)

Use Groovy to Sort XML File

Sorting XML in Groovy

daggett · Accepted Answer · 2020-11-07T02:18:17.877

1

def body='''
<PerPerson>
  <employmentNav>
    <EmpEmployment>
      <jobInfoNav>
        <EmpJob>
          <event>10206</event>
          <userId>abc123</userId>
          <eventReason>RH</eventReason>
          <startDate>2020-10-05T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10186</event>
          <userId>abc123</userId>
          <eventReason>RL</eventReason>
          <startDate>2020-03-11T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10203</event>
          <userId>abc123</userId>
          <eventReason>HN</eventReason>
          <startDate>2019-12-16T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10190</event>
          <userId>abc123</userId>
          <eventReason>Z1</eventReason>
          <startDate>2020-06-13T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10193</event>
          <userId>abc123</userId>
          <eventReason>ZC</eventReason>
          <startDate>2020-03-11T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10189</event>
          <userId>abc123</userId>
          <eventReason>RW</eventReason>
          <startDate>2020-06-13T00:00:00.000</startDate>
        </EmpJob>
        <EmpJob>
          <event>10180</event>
          <userId>abc123</userId>
          <eventReason>FU</eventReason>
          <startDate>2020-04-15T00:00:00.000</startDate>
        </EmpJob>
      </jobInfoNav>
    </EmpEmployment>
  </employmentNav>
</PerPerson>
'''

def xml =  new XmlParser().parseText(body)
def jobInfoNav = xml.employmentNav.EmpEmployment.jobInfoNav[0]
//get value - array of child nodes, sort, and set it back
jobInfoNav.value = jobInfoNav.value().sort{ e-> e.startDate.text() }

groovy.xml.XmlUtil.serialize(xml)

edited Nov 07 '20 at 02:18

answered Nov 07 '20 at 01:45

daggett

26,404
3
40
56

Thank you so much, that worked. How to get a reverse of it? I mean its now in ascending order, how to make it as descending? – Pradeep Bondla Nov 07 '20 at 02:27
Also, this code works when supplied with one user as in question. BUT, if multiple users are supplied with multiple EmpJob nodes for each user then this sorting does NOT work. It won't give error but sorting does not happen for multiple users xml. – Pradeep Bondla Nov 07 '20 at 02:49
`sort{a,b-> b.startDate.text() <=> a.startDate.text()}`. And for multiple users - you have to iterate them. I can't guess how XML looks like for multiple users... – daggett Nov 07 '20 at 03:51
Thank you, it worked too, that's a great help for a newbie/novice in Groovy world. ***Also, edited the xml in my question on how it looks with multiple users. Thanks again.*** – Pradeep Bondla Nov 07 '20 at 04:39
the xml you provided is not valid - there should be a root tag. – daggett Nov 07 '20 at 16:30
Its in SAP CPI coming out of Successfactor's using https and /odata/v2 request and that's how I see in the message body. I process this exact format in groovy script. Thank you. – Pradeep Bondla Nov 08 '20 at 15:19
in groovy `XmlParser` can't parse xml like `1 2` you need a root element like this: ` 1 2 ` – daggett Nov 08 '20 at 15:47
why can't it consider first as root tag? ` details of person 1> details of person 2> details of person 3>` in this top behaving as root correct? I didn't have any issues until now in CPI processing it in groovy or xslt with this type of xml. how can I loop thinking first as root>? `def xml = new XmlParser().parseText(body) xml.PerPerson.each{ sorting code} --- not working xml.PerPerson.PerPerson.each {sorting code} --- not working` Thank you. – Pradeep Bondla Nov 09 '20 at 01:53
ah. that quite strange. i thought it was a mistake – daggett Nov 09 '20 at 01:57

Sort XML child nodes by date field in Groovy Script

1 Answers1