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...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.