TSQL Join and Group Xml Column Values

Question

I have a table like this;

https://i.sstatic.net/QkyB6.png

I want to sum each first 5 columns and string join with comma values in xml column. (It store results like this : <r><p>example</p><p>example2</p></r>)

select 
    sum(Value1), sum(Value2), sum(Value3), sum(Value4), sum(Value5), 
    (select t.c.value('.', 'varchar(400)') + ',' 
     from Xml.nodes('/r/p') as t(c) 
     for xml path('')), 
    DateTime as DateTime 
from table 
group by DateTime

But I'm getting an xml error with group by exception.

A severe error occurred on the current command. The results, if any, should be discarded.

Any help would be awesome for me! :)

Answer 1

I'm afraid you can't do this without subquery:

select
    sum(a.Value1),
    sum(a.Value2),
    sum(a.Value3),
    sum(a.Value4),
    sum(a.Value5),
    stuff(
        (
            select ',' + t.c.value('.', 'varchar(400)')
            from table as b
                outer apply b.[Xml].nodes('r/p') as t(c)
            where b.[DateTime] = a.[DateTime]
            for xml path(''), type
        ).value('.', 'nvarchar(max)')
    ,1,1,'')
from table as a
group by a.[DateTime]

BTW try not to name your columns as types in SQL Server.

sql fiddle example with simplified data