Aggregation query over XML document using T-SQL

Question

I have an XML file with this structure:

<row>
    <id><timestamp>
    <some other fields>
 </row>
 <row>
    <id><timestamp>
    <some other fields>
 </row>

Several rows have the same id but differ in their timestamps.

I want to write a query that extracts the content of the XML into an SQL rowset such that for each id I will get the row with the latest timestamp.

So far, thanks to this SO question, I got to extract the row using:

SELECT T2.Row.query('.')
FROM   #xml_tmp_table
CROSS APPLY XML_TABLE.nodes('/xml_ROOT/section_of_interest') as T2(Row) 

Now, according to SQL Documentation, reading a single column of data from XML row can be done by the value() method.

So I would like to do something like this:

SELECT 
   T2.Row.query('.').value("id_field", "int"),
   max( T2.Row.query('.').value("timestamp_field", "datetime")),
   T2.Row.query('.').value("data_field_1", "decimal(5,2)")),
   T2.Row.query('.').value("data_field_2", "int")),
   ...
   T2.Row.query('.').value("yet_another_data_field", "its_type"))
FROM #xml_tmp_table --or T2 maybe?
group by id_field

So I would get only the rows with the latest timestamp per id value.

Any hints for the right way to accomplish this?

Guess I have to save the result of the first query into a temporary table and apply the value() on its rows.

Answer 1

Extract your values in a derived table and do the group by in the main query.

select T.id,
       max(T.timestamp) as timestamp
from (
     select R.X.value('(id/text())[1]', 'int') as id,
            R.X.value('(timestamp/text())[1]', 'datetime') as timestamp
     from dbo.YourTable as T
       cross apply T.X.nodes('/root/row') as R(X)
    ) as T
group by T.id;

SQL Fiddle