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.

Try firts read data from XML and then do grouping WITH cte AS (SELECT * FROM @xml) SELECT * FROM cte GROUP BY ... — Lukasz Szozda
– Lukasz Szozda, Commented Sep 2, 2015 at 9:24

Mikael Eriksson · Accepted Answer · 2015-09-02 09:36:11Z

4

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

answered Sep 2, 2015 at 9:36

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

@Zack, if an answer works for you, please click it as "accepted". Everybody will see, that the problem is solved and Mikael Erikson is happy about reputation points :-)

Collectives™ on Stack Overflow

Aggregation query over XML document using T-SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related