Xpath 1.0 query to sorting and extract minimum date value doesn't work

Question

I had to extract the oldest date between several nodes and put it in a new xml.

Based on an other thread (I can't find the url sorry) I made this query:

substring-before(string(/*[local-name()='EmployeeUDM_Response']/*[local-name()='Return']/*[local-name()='Employee']/*[local-name()='Contracts']/*[local-name()='Contract']/*[local-name()='EmployementStartDate'][not(. >= following::*[local-name()='EmployementStartDate'])][not(. >= preceding::*[local-name()='EmployementStartDate'])][1]), 'T')

The XML input

<ns0:EmployeeUDM_Response xmlns:ns0="http://Schemas/v2/EmployeeUDM">
    <ns0:Header Type="Employee" Source="Biztalk ESB" Target="EmployeeUDMToRostarCas" Date="2019-04-29T15:24:24" />
    <ns0:Return>
        <ns0:Employee>
            <ns0:ID>f416d1cd-d13f-433c-9297-7fa724b10872</ns0:ID>
            <ns0:Number>200002</ns0:Number>
            <ns0:SagaNumber></ns0:SagaNumber>
            <ns0:SocialSecurityNumber>663993258</ns0:SocialSecurityNumber>
            <ns0:SurNameFull>Streep</ns0:SurNameFull>
            <ns0:FirstName>Meryl</ns0:FirstName>
            <ns0:Gender>Vrouw</ns0:Gender>
            <ns0:DateOfBirth>1988-02-25T00:00:00.0000000+01:00</ns0:DateOfBirth>
            <ns0:Contracts>
                <ns0:Contract>
                    <ns0:ID>9cfae674-da54-418c-9d7f-2f1dfcdc64d3</ns0:ID>
                    <ns0:StartDate>2018-01-01T00:00:00.0000000+01:00</ns0:StartDate>
                    <ns0:EndDate>2019-01-01T00:00:00.0000000+01:00</ns0:EndDate>
                    <ns0:EmployementStartDate>2018-02-01T00:00:00.0000000+01:00</ns0:EmployementStartDate>
                    <ns0:EmployementEndDate>2018-03-01T00:00:00.0000000+01:00</ns0:EmployementEndDate>
                </ns0:Contract>
                <ns0:Contract>
                    <ns0:ID>a584c7d8-a5a1-45a7-9cb9-4dabe9ece851</ns0:ID>
                    <ns0:StartDate>2018-01-01T00:00:00.0000000+01:00</ns0:StartDate>
                    <ns0:EndDate>2019-01-01T00:00:00.0000000+01:00</ns0:EndDate>
                    <ns0:EmployementStartDate>2019-02-01T00:00:00.0000000+01:00</ns0:EmployementStartDate>
                    <ns0:EmployementEndDate>2019-03-01T00:00:00.0000000+01:00</ns0:EmployementEndDate>
                </ns0:Contract>
                <ns0:Contract>
                    <ns0:ID>9a1cfe04-6d4a-4a97-9104-b2633dbb6362</ns0:ID>
                    <ns0:StartDate>2018-01-01T00:00:00.0000000+01:00</ns0:StartDate>
                    <ns0:EndDate>2019-01-01T00:00:00.0000000+01:00</ns0:EndDate>
                    <ns0:EmployementStartDate>2017-02-01T00:00:00.0000000+01:00</ns0:EmployementStartDate>
                    <ns0:EmployementEndDate>2017-03-01T00:00:00.0000000+01:00</ns0:EmployementEndDate>
                </ns0:Contract>
                <ns0:Contract>
                    <ns0:ID>b7f868fe-2b93-45a4-9683-d814e3b6eb4e</ns0:ID>
                    <ns0:StartDate>2018-01-01T00:00:00.0000000+01:00</ns0:StartDate>
                    <ns0:EndDate>2019-01-01T00:00:00.0000000+01:00</ns0:EndDate>
                    <ns0:EmployementStartDate>2016-02-01T00:00:00.0000000+01:00</ns0:EmployementStartDate>
                    <ns0:EmployementEndDate>2016-03-01T00:00:00.0000000+01:00</ns0:EmployementEndDate>
                </ns0:Contract>
                <ns0:Contract>
                    <ns0:ID>4352ead7-8888-4c94-9228-dc0245ae9388</ns0:ID>
                    <ns0:StartDate>2019-01-01T00:00:00.0000000+01:00</ns0:StartDate>
                    <ns0:EndDate>2020-01-01T00:00:00.0000000+01:00</ns0:EndDate>
                    <ns0:EmployementStartDate>2019-01-01T00:00:00.0000000+01:00</ns0:EmployementStartDate>
                    <ns0:EmployementEndDate></ns0:EmployementEndDate>
                </ns0:Contract>
                <ns0:Contract>
                    <ns0:ID>8bf2208c-9f5d-4729-83d9-f10789913a61</ns0:ID>
                    <ns0:StartDate>2018-01-01T00:00:00.0000000+01:00</ns0:StartDate>
                    <ns0:EndDate>2019-01-01T00:00:00.0000000+01:00</ns0:EndDate>
                    <ns0:EmployementStartDate>2015-02-01T00:00:00.0000000+01:00</ns0:EmployementStartDate>
                    <ns0:EmployementEndDate>2015-03-01T00:00:00.0000000+01:00</ns0:EmployementEndDate>
                </ns0:Contract>
                <ns0:Contract>
                    <ns0:ID>c2d3b8e6-ccd5-4a18-b28a-f853e2bc8f0e</ns0:ID>
                    <ns0:StartDate>2018-01-01T00:00:00.0000000+01:00</ns0:StartDate>
                    <ns0:EndDate>2019-01-01T00:00:00.0000000+01:00</ns0:EndDate>
                    <ns0:EmployementStartDate>2014-02-01T00:00:00.0000000+01:00</ns0:EmployementStartDate>
                    <ns0:EmployementEndDate>2014-03-01T00:00:00.0000000+01:00</ns0:EmployementEndDate>
                </ns0:Contract>
            </ns0:Contracts>
        </ns0:Employee>
    </ns0:Return>
</ns0:EmployeeUDM_Response>

The XML output should be:

<ns0:Employee date="2019-04-30"http://SchemasMDSRequest_Schema">
    <Number>200002</Number>
    <SurNameFull>Streep</SurNameFull>
    <FirstName>Meryl</FirstName>
    <GivenName>Meryl</GivenName>
    <EmployementStartDate>2014-02-01</EmployementStartDate>
    <EmployementEndDate>2099-01-01</EmployementEndDate>
</ns0:Employee>

But the actual XML output is:

<ns0:Employee date="2019-04-30"http://SchemasMDSRequest_Schema">
    <Number>200002</Number>
    <SurNameFull>Streep</SurNameFull>
    <FirstName>Meryl</FirstName>
    <GivenName>Meryl</GivenName>
    <EmployementStartDate>2018-02-01</EmployementStartDate>
    <EmployementEndDate>2099-01-01</EmployementEndDate>
</ns0:Employee>

I had to use XPath 1.0 as BizTalk Server 2013 doesn't support XPath 2.0. I don't know what's wrong with the query, I'm pretty new with XPath.

Answer 1

The < and > operators in XPath 1.0 are defined only on numbers, not on strings (or dates/times). If you use them on strings, the system will attempt to convert strings to numbers, generally resulting in a NaN < NaN comparison, which is always false.

For comparing dates a popular XPath 1.0 workaround is to strip out the punctuation and then compare say 20190430 and 20190101 as numbers.