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

I believe this is because SQL-Server does not understand what is the order that you are sorting here, hence it cannot give you the max and min. If you replace the content of the tags to be number (1, 4, 2, 3) instead of (2006-01-01, ....), you will see that it will return a minimum number. — DVT, Aug 09 '16 at 00:07
thanks all. Each answer worked like a charm :). I've chosen Mikael's as I need to query a table with XML column and his way I don't have to do the CROSS APPLY — user2065377, Aug 09 '16 at 14:04
I am not sure though which way is the best way from performance perspective as I am testing with real small sample data set. I guess time will tell :). Thanks a lot. — user2065377, Aug 09 '16 at 14:06

score 4 · Accepted Answer · answered Aug 09 '16 at 06:44

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');

gofr1 · Answer 2 · 2016-08-09T05:53:49.870

The simplest way:

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  MIN(t.v.value('.','date')) minDate,
        COUNT(t.v.value('.','date')) countDate
FROM @xml.nodes('//Plan/EffectiveDate') as t(v)

Output:

minDate     countDate
2006-01-01  4

DVT · Answer 3 · 2016-08-09T00:51:29.647

Try this. I do not think this is the best solution (and I look forward to see a better one here). But it will give you what you want. Edited (The second one which I like better)

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

SELECT
    Min(a.EffDate) AS MinDate
    , Count(a.EffDate) AS CountDate
FROM
(
    SELECT
        EffDate.value('.','DATE') AS EffDate
    FROM
        @x.nodes('/Plan/EffectiveDate') AS Plans(EffDate)
)a;

This is the first one.

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

DECLARE @DocHandle INT;
EXEC sp_XML_PrepareDocument @DocHandle OUTPUT, @x;

SELECT Min(a.EffDate) AS MinDate
, Count(a.EffDate) AS CountDate
FROM
(
SELECT *
FROM OPENXML(@DocHandle,'/Plan/EffectiveDate')
WITH (EffDate NVARCHAR(50) '.')
) a

EXEC sp_XML_RemoveDocument @DocHandle;

MIN of XML descendats of date type

3 Answers3