MIN of XML descendats of date type

Question

How can I query min date of all descendants in XML field. Something like this:

DECLARE @xml XML ='
<Plan>
    <EffectiveDate>2006-01-01</EffectiveDate>
    <EffectiveDate>2016-09-14</EffectiveDate>
    <EffectiveDate>2016-09-14</EffectiveDate>
    <EffectiveDate>2016-09-14</EffectiveDate>
</Plan>'

SELECT @xml.value('min(descendant::EffectiveDate)','varchar(max)') minDate
    ,@xml.value('count(descendant::EffectiveDate)','varchar(max)') countDate

unfortunately above returns NULL for MIN

Answer 1

The default datatype for untyped xml node values is xdt:untypedAtomic. The min Function (XQuery) will try to convert xdt:untypedAtomic values to xs:double and then return the min value. Your values can not be converted to xs:double so min() will return the min value of four null values.

Since min() works just fine on xs:date you can first convert your values to xs:date and then do the min() over the date values.

declare @xml xml = '
<Plan>
    <EffectiveDate>2006-01-01</EffectiveDate>
    <EffectiveDate>2016-09-14</EffectiveDate>
    <EffectiveDate>2016-09-14</EffectiveDate>
    <EffectiveDate>2016-09-14</EffectiveDate>
</Plan>';

select @xml.value('min(for $n in descendant::EffectiveDate/text() 
                       return $n cast as xs:date?)', 'date'),
       @xml.value('count(descendant::EffectiveDate)', 'int');