SQL Server / XML: change level of nested query results

can someone tell me how I have to change the following so that the results of the nested query (items) appear one level below dateRange instead of on the same level (which is what I get at present) ?


SELECT      A.dateRange,
                    SELECT      B.item,
                                        SELECT      COUNT(*) AS volume
                                        FROM        LogEsc C
                                        WHERE       C.policy = B.item
                                        AND         C.EID LIKE 'PE%'
                                        AND         C.dateEsc LIKE A.dateRange + '%'
                                        FOR XML PATH(''), ELEMENTS, TYPE
                    FROM        @temp2 B
                    ORDER BY    B.ranking, B.item
                    FOR XML PATH(''), ELEMENTS, TYPE
            ) AS items
FROM    @temp A

My current XML:


Many thanks for any help with this, Mike.


Change AS items to AS [dateRange/items].

Need Your Help

C# override ~ to take a function

c# operators overloading

I ideally I want to implement something like var queryResult = ~( () =&gt; function(arg) );

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.