使用T-SQL对XML文档进行聚合查询

Question

我有一个具有以下结构的XML文件：

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

有几行具有相同的id但时间戳不同。

我想编写一个查询，将XML内容提取到SQL行集中，以便对于每个id，我将获得具有最新时间戳的行。

到目前为止，由于这个SO问题，我已经使用以下内容提取了行：

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

现在，根据SQL文档，从XML行中读取单个数据列可以使用value()方法。

因此，我想要做这样的事情：

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

我希望只获取每个id值的最新时间戳对应的行。

有什么提示可以帮助正确实现这个功能吗？

我猜需要将第一个查询的结果保存到一个临时表中，并在其行上应用value()函数。

- Zack

---

首先尝试从XML中读取数据，然后进行分组 WITH cte AS (SELECT * FROM @xml) SELECT * FROM cte GROUP BY ... - Lukasz Szozda

Answer 1

在衍生表中提取所需的值，然后在主查询中进行分组。

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

- Mikael Eriksson

---

@Zack，如果有一个答案对你有用，请将其点击为“已采纳”。所有人都会看到问题已经解决，Mikael Erikson也会因声望点数而感到高兴 :-) - Shnugo