cancel
Showing results for 
Search instead for 
Did you mean: 

Groovy or XSLT to filter XML Payload based on TypeCode & Max Date (LastUpdatedOn)

kai_o
Participant
0 Kudos
2,868

Hi, I have a requirement to filter an XML Payload (see code below) , first get only the Nodes where TypeCode=10009 and than get only the latest one where Max(LastUpdatedOn ) .

I am able to make the first filter with following XSLT :

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:mode on-no-match="shallow-copy" />

<xsl:output method="xml" indent="yes" />

<xsl:template match="ServiceRequestAttachmentFolder[TypeCode != '10009']" />

</xsl:stylesheet>

I tried adding the <xsl:value-of select="max(//LastUpdatedOn/xs:date(.))"/>

However , I'm having trouble with the 2nd step of selecting only the latest Node (Max(LastUpdatedOn )).

I think it is related to the Date Format (<LastUpdatedOn>2019-05-13T20:18:42.283</LastUpdatedOn>) .

Any help would be appreciated (best if I can achieve both in my XSLT step ) , also as additional groovy or xslt .

Thanks

Kai

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:mode on-no-match="shallow-copy" />

<xsl:output method="xml" indent="yes" />

<xsl:template match="ServiceRequestAttachmentFolder[TypeCode != '10009']" />

</xsl:stylesheet>

<ServiceRequestCollection>
<ServiceRequest>
<ServiceRequestAttachmentFolder>
<ServiceRequestAttachmentFolder>
<LastUpdatedBy>Rachel</LastUpdatedBy>
<CreatedBy>Rachel</CreatedBy>
<ObjectID>00163E832D181ED99E973FCF9207F8C4</ObjectID>
<TypeCode>10001</TypeCode>
<TypeCodeText>Standard Attachment</TypeCodeText>
<LastUpdatedOn>2019-05-17T15:42:59.661</LastUpdatedOn>
<Binary>AAAAAAAAAAAAAAAAAAAAAABEAQAAIgAAAAAAAABfAF8AcwB1AGIAcwB0AGcAMQAuADAAXwA0ADAAMwA5ADAAMAAxAEYAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAKgACAf///////////////wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEMBAAAcAAAAAAAAAF8AXwBzAHUAYgBzAHQAZwAxAC4AMABfADUARAAwADEAMAAwADEARgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAqAAIBOAAAADwAAAD/////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQQEAAEIAAAAAAAAAXwBfAHMAdQBiAHMAdABnADEALgAwAF8ANQBEADAAMgAwADAAMQBGAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACoAAgH///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA/AQAAQgAAAAAAAABfAF8AcwB1AGIAcwB0AGcAMQAuADAAXwA1AEQAMAA3ADAAMAAxAEYAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAKgACATsAAAA9AAAA/</Binary>
<CreatedOn>2019-05-17T15:39:44.932</CreatedOn>
<ParentObjectID>00163E832D2E1ED994CC0370E655E011</ParentObjectID>
<ServiceRequestID>39979 </ServiceRequestID>
</ServiceRequestAttachmentFolder>
<ServiceRequestAttachmentFolder>
<LastUpdatedBy>Etienne</LastUpdatedBy>
<CreatedBy>Etienne</CreatedBy>
<ObjectID>00163E832D1C1EE99DB789A6A6A639E0</ObjectID>
<TypeCode>10009</TypeCode>
<TypeCodeText>Summary</TypeCodeText>
<LastUpdatedOn>2019-05-13T20:18:42.283</LastUpdatedOn>
<Binary>JVBERi0xLjYNJeLjz9MNCjIzMyAwIG9iago8PC9GaWx0ZXIvRmxhdGVEZWNvZGUvRmlyc3QgMTYxOS9MZW5ndGggMjcwMS9OIDE5NC9UeXBlL09ialN0bT4+c3RyZWFtDQpo3sRZTXMjtxH9Kzh6D7HQ+GgAKZerVFG5srX2ZiPJyWHKBy41KzORyA01cnn/vV8D4FBakU3lklw4w5lG90O/1xhMTzTWsCHrTDIUg8mGSjHFOB9w1TgmQ2RccYac8VQMeeMT7gUTfDaOTMjOOGeimHkTGWODYRuNi4aZ4cJwgWUyCacumxTxr5iU2XhrMmzREYwRDExREIyMTEwQTAwMDAwMDAwMDA4QTlEPjxFQ0REM0RGMEQxMURCMjExMEEwMDAwMDAwMDAwOEE5RD5dL0luZm8gMjMyIDAgUi9MZW5ndGggMTg2L1Jvb3QgMSAwIFIvU2l6ZSAyMzkvVHlwZS9YUmVmL1dbMSAyIDFdPj5zdHJlYW0NCmjeYmIAAiZG/hQGJgaGaiDrIy8D0z8GEQYmJoaXIDEGRlIIObyy90g2j8YEO4jgo9SU55QawDfYwmWUIJtghrP+wlkCWNR9grNEUCU+onI5KJD9BWMxhoMI1OzM+B9GgLn/GZn+y6f/A7IcGKAEMw+Q4PEDEtzvgQTjB5AEiOC4DZJVAbEyQGJzQLJgYhqIOxXGZQBxGbFwGeCKIVw4a9UpkG0fQWI/gQRTEkjCH0h4WDMABBgATfQnHAplbmRzdHJlYW0KZW5kb2JqCnN0YXJ0eHJlZgoyMjI0NgolJUVPRgo=</Binary>
<CreatedOn>2019-05-13T20:18:42.229</CreatedOn>
<ParentObjectID>00163E832D2E1ED994CC0370E655E011</ParentObjectID>
<ServiceRequestID>39979 </ServiceRequestID>
</ServiceRequestAttachmentFolder>
<ServiceRequestAttachmentFolder>
<LastUpdatedBy>Etienne</LastUpdatedBy>
<CreatedBy>Etienne</CreatedBy>
<ObjectID>00163E832D281EE99CA04C558DF7EABB</ObjectID>
<TypeCode>10009</TypeCode>
<TypeCodeText>Summary</TypeCodeText>
<LastUpdatedOn>2019-05-07T19:57:47.249</LastUpdatedOn>
<Binary>JVBERi0xLjYNJeLjz9MNCjE4MSAwIG9iago8PC9GaWx0ZXIvRmxhdGVEZWNvZGUvRmlyc3QgMTI1OS9MZW5ndGggMjIwOC9OIDE1NC9UeXBlL09ialN0bT4+c3RyZWFtDQpo3sRYTXPjuBH9KziOD1nj+yO1tVWuuLYyNbsTR9YmB9YcaAnjZSJRDkVNjf/9vgYg2hqL0OSSXASKfN390P0aBGEYZ5YJLpljwmjmmQiBBSaVxl0mrWBCMBkkE5IpEZhQTDk800wrz6Rg2ksmJTMEU8xY2GpmuWHSMGstXDAbgHTM4VJ65gz+Bea8ZYozLzRlKEQ6MjAxOTA1MDcxOTU3NDdaKS9Qcm9kdWNlcihBZG9iZSBYTUwgRm9ybSBNb2R1bGUgTGlicmFyeSk+PgplbmRvYmoKMTg2IDAgb2JqCjw8L0RlY29kZVBhcm1zPDwvQ29sdW1ucyA0L1ByZWRpY3RvciAxMj4+L0ZpbHRlci9GbGF0ZURlY29kZS9JRFs8QUYyMkRCRDZEMTFEQjIxMTBBMDAwMDAwMDAwMDhBMkQ+PDlDM0NEQkQ2RDExREIyMTEwQTAwMDAwMDAwMDA4QTJEPl0vSW5mbyAxODAgMCBSL0xlbmd0aCAxNzAvUm9vdCAxIDAgUi9TaXplIDE4Ny9UeXBlL1hSZWYvV1sxIDIgMV0+PnN0cmVhbQ0KaN5iYgACJkZebwYmBoZqIOvzJwamfwweDExMDFtBYgyMpBByeGXvkWwejQl2EMFHqSnPKTWAb7CFywAQzHDWXzhLAIu6T3CWCKrER1QuBwWyv2AsxnoQgZoNGP/DCDD3PyPTf9mmf0CWAwOUYOYHEjx+QIL7PUjsA5BgBBHsbSBZFRArAyQ2B0RMAymZCiS2BgIJThaQ2E8QYQoiAoCEw1kGgAADAIFaHyMKZW5kc3RyZWFtCmVuZG9iagpzdGFydHhyZWYKMTk1NTEKJSVFT0YK</Binary>
<CreatedOn>2019-05-07T19:57:47.200</CreatedOn>
<ParentObjectID>00163E832D2E1ED994CC0370E655E011</ParentObjectID>
<ServiceRequestID>39979 </ServiceRequestID>
</ServiceRequestAttachmentFolder>
<ServiceRequestAttachmentFolder>
<LastUpdatedBy>Kai</LastUpdatedBy>
<CreatedBy>Kai</CreatedBy>
<ObjectID>3DCC58FFBD421EEE968A5A720FB97C76</ObjectID>
<TypeCode>10009</TypeCode>
<TypeCodeText>Summary</TypeCodeText>
<LastUpdatedOn>2023-09-21T07:45:26.254</LastUpdatedOn>
<Binary>JVBERi0xLjYNJeLjz9MNCjI0NSAwIG9iago8PC9GaWx0ZXIvRmxhdGVEZWNvZGUvRmlyc3QgMTY3Ny9MZW5ndGggMjg1MS9OIDIwMC9UeXBlL09ialN0bT4+c3RyZWFtDQpo3sxaW28ctxX+KwT6Ej004uGdRRBAkOtaUJKqktwkHhjGejWRt5V2ldXKqf99v0NyZiTtkHaf3Id4qD3k+c6dlxMvpAiCpBJRkDUYCSXxAwmlnSAlVMSPWmilBBmhHU8TRlpBThhLgrwwAZ8gLIYUhXXMQtiIDwmnnMBS5zwYChfxMcIrLDdSeAcIQyJIYBglguGvFsHz14jIMMaKqPnrREzzgSIlD1huFtJAJuk1BJMQnHgAgchCYv6PAEoWvypVdFQ2ChZBRWBjqsb8gJk6OhE0Q8MoLIILIjB0jCI4ZmIhG76YFIDuYJEAng7AUJe8JBHZJBrmBD+PfyL4BRUEmzF4/A1+UWrogq+OgoWLWBQDrAZLAUpJyxqx/eAAAlPFVmEnKWJ5sVwpzb8YDBw4wlBKS/7FseMkGwQDFgLeVEbxKnA2bAf2sWE92cuW7cl+tp4H4OwwkaCZcmxheFs55gN/Kw/nEdysvOPl4BxSsIBzgM0IflfBYznwVIQVYHMMHNscMSRZpL1Byb2R1Y2VyKEFkb2JlIFhNTCBGb3JtIE1vZHVsZSBMaWJyYXJ5KT4+CmVuZG9iagoyNTEgMCBvYmoKPDwvRGVjb2RlUGFybXM8PC9Db2x1bW5zIDQvUHJlZGljdG9yIDEyPj4vRmlsdGVyL0ZsYXRlRGVjb2RlL0lEWzxCQzNBOTY3MUQyMURCMjExMEEwMDUxRjYyMDg2QjdGRj48NDA1NDk2NzFEMjFEQjIxMTBBMDA1MUY2NDQ4OEI3RkY+XS9JbmZvIDI0NCAwIFIvTGVuZ3RoIDE4Mi9Sb290IDEgMCBSL1NpemUgMjUyL1R5cGUvWFJlZi9XWzEgMiAxXT4+c3RyZWFtDQpo3mJiAAImRoHfDEwMjNVA1vtmBqZ/DOwMTEwMPxmZgJIg4s8/GIvqhACIsKGZ8UOW6B8NA2KIbdQzqoQWKRvM4sOnjtESh8T/jZS64DHVPMPkB3IpA6oYtFD4r7TmP5DlACpBQASLGpAQ0AcS3H4g4j1I4gOMYDAEsX4CCY5/INYqGMGwBs7dAOLCJRjXwLkb4IrhEhAuSGKeE8i2dpDYNRAhCCISgARTCpAI1mEACDAA9VMmywplbmRzdHJlYW0KZW5kb2JqCnN0YXJ0eHJlZgoyNTQ1NAolJUVPRgo=</Binary>
<CreatedOn>2023-09-21T07:45:26.183</CreatedOn>
<ParentObjectID>00163E832D2E1ED994CC0370E655E011</ParentObjectID>
<ServiceRequestID>39979 </ServiceRequestID>
</ServiceRequestAttachmentFolder>
<ServiceRequestAttachmentFolder>
<LastUpdatedBy>Kai</LastUpdatedBy>
<CreatedBy>Kai</CreatedBy>
<ObjectID>3DCC58FFBD421EEE968A5AB4BCC11C79</ObjectID>
<TypeCode>10087</TypeCode>
<TypeCodeText>Graphical Signature of Buyer</TypeCodeText>
<LastUpdatedOn>2023-09-21T07:45:26.254</LastUpdatedOn>
<Binary>iVBORw0KGgoAAAANSUhEUgAAASIAAAB4CAYAAABW3P+TAAAAAXNSR0IArs4c6QAAEvhJREFUeAHtXUvo3cQbndZqbatV+5DWFvurGxcufFV8UOtC0IWia5FiRVy4cuHKlboRlwouxAf4xK1LoSoIFRWLj6WItkVRwbZqra1v//dEzv3PzS+PSTKTZCZnIDfJZOb7vjkzc/LNTJK74t9ZMApCQAgIgQERWDmgbqkWAkJACGQIiIjUEISAEBgcARHR4FUgA4SAEBARqQ0IASEwOAIiosGrQAYIASEgIlIbEAJCYHAERESDV4EMEAJCQESkNiAEhMDgCKwa3AIZMAoEzjjjDPPPP/8s2LJixYplcQsJdCIEPCEgj8gTkLGLyZMQyqOH7mOv1XjsFxHFU1fBLYVXBPIRAQWHWgpyCIiIcoBM8XTVqv9G6H/99dcUi68yjwABEdEIKmFoE4qGZUPbJP3TQkBENK36LiwthmKYmFYQAkMhoFWzoZAfiV4OyzZu3BjcopUrVy7MP2lVLjjk0ShYMbsb6ntE0VSXf0PpCeWbQVl8GwvWrl1rTp8+XZg1r7cwkSKTR0BDs+SruLyAmzdvzi5itSxUgGyS0Jo1a7QqFwroyOXKI4q8AruYz6FSkVfiyyOCHGz5CXFf8ruUX3nHg4A8ovHURe+WgIBICCGUc/7p0ksvDSFeMhNCYDCPCC7733//nRCU8RWlzFtBSUhQRd6Sa0mrZFRdc5WvdOkg0LtHhHkJNEK46meddVY6SEZaEgzPQgR6QSHnn0LYLZnDINDr8j1cdXpBIKHff/99mFJLqt009Z6SbQEqWG8IgIQeeugh8+6775rdu3f3pjcGRUkQ0ebNmw1eUMQckcu3kjkk27Bhg556jaGVJmQj3kETCS2v0CSIiMTi4g25vgC7HCrFCAEhEAqBJOaIXAgIAOIFWJe38EOBLblCQAgUI5AEERUXbXksnnJFwAuwCkJACIwHgSSGZi4T1Ry+YbWs6qP546kaWSIEpoPAJDwiTGJj+AYyEglNp3GrpPEgkDwR8WlrkBD/GSKe6pGlQmAaCKxKuZgcjomEUq5llS0FBJL0iNauXZu96sHhmDyhFJqqypAyAskREYZip0+fzupszZo1Go6l3HpVtmQQSGpoxvmgJi++JlOTKogQiBiBpDwi/AWQSCji1ijTJ4tAEkTE54gwKa3/I5tsW1bBI0YgiQcaI8ZfpgsBITBDIAmPSDUpBIRA3AiIiOKuP1kvBJJAQESURDWqEEIgbgRERHHXn6wXAkkgICJKohpVCCEQNwIiorjrT9YLgSQQEBElUY0qhBCIGwERUdz1J+uFQBIIiIiSqEYVQgjEjcD/ABkYfVR8jsNhAAAAAElFTkSuQmCC</Binary>
<CreatedOn>2023-09-21T07:45:30.113</CreatedOn>
<ParentObjectID>00163E832D2E1ED994CC0370E655E011</ParentObjectID>
<ServiceRequestID>39979 </ServiceRequestID>
</ServiceRequestAttachmentFolder>
<ServiceRequestAttachmentFolder>
<LastUpdatedBy>Kai</LastUpdatedBy>
<CreatedBy>Kai</CreatedBy>
<ObjectID>3DCC58FFBD421EEE968A5AB4BCC17C79</ObjectID>
<TypeCode>10095</TypeCode>
<TypeCodeText>Graphical Signature of Technician</TypeCodeText>
<LastUpdatedOn>2023-09-21T07:45:26.254</LastUpdatedOn>
<Binary>iVBORw0KGgoAAAANSUhEUgAAASIAAAB4CAYAAABW3P+TAAAAAXNSR0IArs4c6QAAEmtJREFUeAHtXWmMFUUXLRAQRWcQd4wwDoyIihuyBFEmCBJ+KC64BZc/RsUEcdf4AyIhAaIoJApo0B8STGQmIKMxGgUkSgyLTgAjaFxQEzdURBFQQb8+ZW5//R7dPd39eqnuPpW8dL/uWm6dqj5969btqk7/WkExEAEiQAQyRKBzhmWzaCJABIiARoBExI5ABIhA5giQiDJvAgpABIgAiYh9gAgQgcwRIBFl3gQUgAgQARIR+wARIAKZI0AiyrwJKAARIAIkIvYBIkAEMkeARJR5E1AAIkAESETsA0SACGSOAIko8yagAESACJCI2AeIABHIHAESUeZNQAGIABEgEbEPEAEikDkCJKLMm4ACEAEiQCJiHyACRCBzBEhEmTeBWQKMHTtWde7cWXXq1Mn+4f+kSZPMEpTSFAqBTlykNFaNc1IhiBNOErOCAiHWw4RLgdAuAQToPi69h6VwsbH/yySerLVu2cK95EzpVCjJQI0oB5KSLGD58uNZ6QDYN1m6lIByQEP7PnDlTn+eBhODVPWjQID0kw5f93bt3Txo65m8IAtSIDGmIsGLAC1oIR9KCeDAMM3kaXmStPra2tqobbrhBXX311WrZsmXVt/m/4AhQIzKogUEiIBhoMdUBi3zhPsgGP9h5ROsZOnSorfXkkYSw9fb111+vpk6dShKqbviS/OcuHgY0tHO9n47EESKCERcG3bwHOCqCiObOnavuueeevFeH8kdEgBpRRODiTCbajRiY4UEMchLSwRGbFeI+hmOIXwQSgqPi/PnztaMiSSjOHpW/vKgR5a/Nci8xyPTiiy9WmzZtUqtWrVIjR47MfZ1YgdoQoLG6NvyYOiQCdFQMCVhJopOIStLQJlQTjorY9hkBNi4u6WpCq5ghA21EZrRD4aWAoyIcLWHrgtMlSajwTR6qgiSiUHAxchQE6KgYBbVypSERlau9U69tS0uLam5u1lv9rFu3TvtCpS4ECzQeARKR8U2UXwHXr1+vvaXpqJjfNkxLck7fp4V0SctZvXq1wpY/DETADwESkR86vEcEiEAqCHBolgrMLIQIEAE/BEhEfujwHhEgAqkgQCJKBWYWQgSIgB8CJCI/dHiPCBCBVBAgEaUCMwshAkTADwESkR86vEcEiEAqCJCIUoGZhRABIuCHAInIDx3eIwJEIBUESESpwMxCiAAR8EOAROSHDu8RASKQCgIkolRgZiFEgAj4IUAi8kOH94gAEUgFARJRKjCzECJABPwQIBH5ocN7RIAIpIIAiSgVmFkIESACfgj8D2bBQLqLj2XVAAAAAElFTkSuQmCC</Binary>
<CreatedOn>2023-09-21T07:45:30.208</CreatedOn>
<ParentObjectID>00163E832D2E1ED994CC0370E655E011</ParentObjectID>
<ServiceRequestID>39979 </ServiceRequestID>
</ServiceRequestAttachmentFolder>
</ServiceRequestAttachmentFolder>
</ServiceRequest>
</ServiceRequestCollection><br>

Accepted Solutions (1)

Accepted Solutions (1)

DG
Active Contributor

Hi Kai

You can use XSLT 2 or 3 they are more powerful.

<xsl:stylesheet version="2.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xsl:output method="xml" indent="yes"/>
    
    <xsl:template match="/ServiceRequestCollection">
        <xsl:copy>
            <xsl:for-each-group select="ServiceRequest/ServiceRequestAttachmentFolder/ServiceRequestAttachmentFolder[TypeCode='10009']" 
                                group-by="ServiceRequestID">
                <xsl:variable name="maxDateTime" select="max(current-group()/xs:dateTime(LastUpdatedOn))"/>
                <xsl:for-each select="current-group()">
                    <xsl:sort select="xs:dateTime(LastUpdatedOn)" order="descending"/>
                    <xsl:if test="xs:dateTime(LastUpdatedOn) = $maxDateTime">
                        <xsl:copy-of select="."/>
                    </xsl:if>
                </xsl:for-each>
            </xsl:for-each-group>
        </xsl:copy>
    </xsl:template>
</xsl:stylesheet>

Daniel

kai_o
Participant
0 Kudos

Hey Daniel,

Thanks for your help .

This is doing the work perfectly and results as expected .

Regards,

Kai

MortenWittrock
Active Contributor
0 Kudos

Guys, the structure of the output document is not identical to the input document? The ServiceRequest and ServiceRequest/ServiceRequestAttachmentFolder elements have disappeared from the output.

kaor was approaching this correctly by filtering, i.e. you don't have to build up the correct document, you only need to remove the elements you don't need.

Regards,

Morten

Answers (1)

Answers (1)

MortenWittrock
Active Contributor

Hi Kai

You were halfway there with your solution. To make it work, add a variable containing the dateTime of the latest update and check it in your filter condition. Like this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xsl:mode on-no-match="shallow-copy" />

    <xsl:output method="xml" indent="yes" />

    <xsl:variable name="latestUpdate" select="max(//ServiceRequestAttachmentFolder[TypeCode = '10009']/LastUpdatedOn/xs:dateTime(.))" />

    <xsl:template match="ServiceRequestAttachmentFolder/ServiceRequestAttachmentFolder[TypeCode != '10009' or xs:dateTime(LastUpdatedOn) lt $latestUpdate]" />

</xsl:stylesheet>

Regards,

Morten

kai_o
Participant
0 Kudos

Hi Morten,

Nice and clean !

I've simulated now in the iFlow and it works as well .

Thanks a lot for helping out .

Regards,

Kai

DG
Active Contributor
0 Kudos

Nice. I guess that will work on older XSLT

MortenWittrock
Active Contributor
0 Kudos

Hi daniel.graversen4

Actually not - that on-no-match="shallow-copy" identity transformation is specific to XSLT 3.

Regards,

Morten