CODEWITHSUNDEEP
sqlsql-serverxml
Chris B
Chris B

Reputation: 1

SQL query to get most recent date from XML document

I am doing a SQL query against a column with an XML document located.

The XML document looks like the following.

<root>
    <date>2016-10-12</date>
    <date>2016-12-01</date>
    <date>2016-11-13</date>
</root>

As you can see the dates are out of order.

I am looking for a SQL query that will get the most recent date from the XML document (in this case: 2016-12-01).

Upvotes: 0

Views: 194

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

Try this

DECLARE @xml XML

SET @xml = '<root>
    <date>2016-10-12</date>
    <date>2016-12-01</date>
    <date>2016-11-13</date>
     </root>'

SELECT Top 1 x.col.value('.', 'date') AS dates
FROM   @xml.nodes('/root/date') x(col)
ORDER  BY dates DESC

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

One way is to read all data and find the maximum externally (external ORDER BY with TOP 1, like in Prdp's answer, or MAX(), eventually with GROUP BY).

Another way is a FLWOR-XQuery:

DECLARE @xml XML=
'<root>
    <date>2016-10-12</date>
    <date>2016-12-01</date>
    <date>2016-11-13</date>
</root>';

SELECT @xml.value('max(for $d in /root/date return xs:date($d))','date')

This means:

Take each value in /root/date, return it as date and find the highest!

Both approaches will need to read the whole list, but it should be a bit faster only to look for the maximum value, rather than return a full list and do some external sorting, picking again...

Upvotes: 1

Related Questions