How do I select a maximum date from SQL Server 2005 XML Column?

I am new to XML programming in SQL Server. This will probably be a simple question for you guys!

I have a database table called TestXML, in SQL Server 2005. It has one column testXML with xml datatype. The data in this column is in the following format:

<TEST name="testName" status="Completed">
    <Status CompletedOn="2011-11-01T01:12:13Z"/>
    <Bar number="1" status="Pending">
        <Control RequestDate="2011-11-30T01:12:13Z"/>
    </Bar>
    <Bar number="2" status="Pending">
        <Control RequestDate="2011-11-30T01:11:13Z"/>
    </Bar>
    <Bar number="3" status="Pending">
        <Control RequestDate="2011-11-30T01:13:13Z"/>
    </Bar>
</TEST>

I want to write a query, that will return max RequestDate from Bar/Control/@RequestDate, so in the example above, I want my query to return - 2011-11-30T01:13:13Z

So far, I have tried:

SELECT testXML.query('max(//@RequestDate)') from TestXml
WHERE testXML.value('(/TEST/@status)[1]', 'varchar(20)') = 'Completed'

This returns a blank value.... and if I try with testXML.query('max(//string(@RequestDate))') I get following error:

XQuery [TestXml.testXML.query()]: The XQuery syntax '/function()' is not supported.

Please could someone help me write this query. Many thanks in advance.

Answers


I have managed to get it working using a temp table. Not very happy with the solution, but thinking if there is better way to write this:

SELECT  testXML.value('(/TEST/@name)[1]', 'varchar(100)') Name,
        testXML.value('(/TEST/@status)[1]', 'varchar(100)') StatusCol,
        Bar.Ctrl.value('(Control/@RequestDate)[1]', 'varchar(100)') RequestDate
INTO #dates
     -- Bar.Ctrl.query('max(data(Control/@RequestDate))').value('.', 'datetime')
FROM TestXML
CROSS APPLY testXML.nodes('/TEST/Bar') Bar(Ctrl)
WHERE testXML.value('(/TEST/@status)[1]', 'varchar(100)') = 'Completed'

SELECT Name, StatusCol, max(RequestDate) FROM #dates GROUP BY StatusCol, Name

Need Your Help

Can't unwrap Optional.None error in swift

ios swift ios8

When the value is passed for UILabel the error appears :

How to find out the name of the common documents folder on a network machine

delphi networking unc delphi-2006 mapped-drive

Given that I am executing an EXE file (D2006 app) on a machine across the network, how can I get the pathname to the commondocs folder on that machine, given that the EXE might have been invoked fr...