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) ?

My SQL:

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
FOR XML PATH(''), ELEMENTS, TYPE

My current XML:

  <dateRange>2014-04</dateRange>
  <items>
    <item>EIA</item>
    <volume>17</volume>
    <item>CG</item>
    <volume>12</volume>
    <item>T</item>
    <volume>10</volume>
    <item>AD</item>
    <volume>9</volume>
    <item>IR</item>
    <volume>9</volume>
    <item>AS</item>
    <volume>8</volume>
    <item>A</item>
    <volume>8</volume>
    <item>FS</item>
    <volume>6</volume>
    <item>AP</item>
    <volume>5</volume>
    <item>DG</item>
    <volume>5</volume>
  </items>

Many thanks for any help with this, Mike.

Answers


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.