SQL XML grouping

Question

I have a SQL View with the data of:

+-----------+-------------+--------------+--------------------+------------------+-----------+----------------+--------------+-------------+----------+
| CarparkNo | CarparkAbbr | CarparkDesig | CountingCategoryNo | CountingCategory | FreeLimit | OccupancyLimit | CurrentLevel | Reservation | Capacity |
+-----------+-------------+--------------+--------------------+------------------+-----------+----------------+--------------+-------------+----------+
|         0 | L72         | Lot 72       |                  1 | Short-Term       |       174 |            175 |           87 |           0 |      175 |
|         0 | L72         | Lot 72       |                  2 | Contract         |         0 |              0 |            0 |           0 |        0 |
|         0 | L72         | Lot 72       |                  3 | Total            |       174 |            175 |           87 |           0 |      175 |
+-----------+-------------+--------------+--------------------+------------------+-----------+----------------+--------------+-------------+----------+

I need to make a XML file that looks like

<Carparks>
  <Carpark>
    <CarparkNo>0</CarparkNo>
    <CarparkAbbr>L72</CarparkAbbr>
    <CarparkDesig>Lot 72</CarparkDesig>
    <CountingCategory>
      <CountingCategoryNo>1</CountingCategoryNo>
      <CountingCategory>Short-Term</CountingCategory>
      <FreeLimit>174</FreeLimit>
      <OccupancyLimit>175</OccupancyLimit>
      <CurrentLevel>64</CurrentLevel>
      <Reservation>0</Reservation>
      <Capacity>175</Capacity>
    </CountingCategory>
    <CountingCategory>
      <CountingCategoryNo>2</CountingCategoryNo>
      <CountingCategory>Contract</CountingCategory>
      <FreeLimit>0</FreeLimit>
      <OccupancyLimit>0</OccupancyLimit>
      <CurrentLevel>0</CurrentLevel>
      <Reservation>0</Reservation>
      <Capacity>0</Capacity>
    </CountingCategory>
    <CountingCategory>
      <CountingCategoryNo>3</CountingCategoryNo>
      <CountingCategory>Total</CountingCategory>
      <FreeLimit>174</FreeLimit>
      <OccupancyLimit>175</OccupancyLimit>
      <CurrentLevel>64</CurrentLevel>
      <Reservation>0</Reservation>
      <Capacity>175</Capacity>
    </CountingCategory>
  </Carpark>
</Carparks>

So far, I have a statement of:

SELECT [CarparkNo]
    ,[CarparkAbbr]
    ,[CarparkDesig]
    ,(SELECT [CountingCategoryNo]
        ,[CountingCategory]
        ,[FreeLimit]
        ,[OccupancyLimit]
        ,[CurrentLevel]
        ,[Reservation]
        ,[Capacity]
        FOR XML PATH('CountingCategory'), TYPE)
    FROM [PARK_DB].[dbo].[CPCounting]
    FOR XML PATH('Carpark'), ROOT('Carparks'), TYPE;

Which sort of works, but makes duplicate <Carpark> tags instead of keeping it in one grouping:

<Carparks>
  <Carpark>
    <CarparkNo>0</CarparkNo>
    <CarparkAbbr>L72</CarparkAbbr>
    <CarparkDesig>Lot 72</CarparkDesig>
    <CountingCategory>
      <CountingCategoryNo>1</CountingCategoryNo>
      <CountingCategory>Short-Term</CountingCategory>
      <FreeLimit>174</FreeLimit>
      <OccupancyLimit>175</OccupancyLimit>
      <CurrentLevel>82</CurrentLevel>
      <Reservation>0</Reservation>
      <Capacity>175</Capacity>
    </CountingCategory>
  </Carpark>
  <Carpark>
    <CarparkNo>0</CarparkNo>
    <CarparkAbbr>L72</CarparkAbbr>
    <CarparkDesig>Lot 72</CarparkDesig>
    <CountingCategory>
      <CountingCategoryNo>2</CountingCategoryNo>
      <CountingCategory>Contract</CountingCategory>
      <FreeLimit>0</FreeLimit>
      <OccupancyLimit>0</OccupancyLimit>
      <CurrentLevel>0</CurrentLevel>
      <Reservation>0</Reservation>
      <Capacity>0</Capacity>
    </CountingCategory>
  </Carpark>
  <Carpark>
    <CarparkNo>0</CarparkNo>
    <CarparkAbbr>L72</CarparkAbbr>
    <CarparkDesig>Lot 72</CarparkDesig>
    <CountingCategory>
      <CountingCategoryNo>3</CountingCategoryNo>
      <CountingCategory>Total</CountingCategory>
      <FreeLimit>174</FreeLimit>
      <OccupancyLimit>175</OccupancyLimit>
      <CurrentLevel>82</CurrentLevel>
      <Reservation>0</Reservation>
      <Capacity>175</Capacity>
    </CountingCategory>
  </Carpark>
</Carparks>

How do I go about formatting this to the required spec? The account the command is run under only has read access to the views that the system provides.

I cannot see what you mean. One Tag is `Carparks` and one is `Carpark`. What do you mean with duplicate? — Joshua K, Aug 25 '17 at 18:01
So `Carparks` is the root, while `Carpark` is for if/when we have multiple locations. It should be grouped by the `CarparkNo`. I'm not sure how to go about doing that. — Bennett Blodinger, Aug 25 '17 at 18:09
alright got it. possible duplicate: https://stackoverflow.com/questions/18132039/sql-server-two-level-group-by-with-xml-output — Joshua K, Aug 25 '17 at 18:27

score 1 · Accepted Answer · answered Aug 25 '17 at 18:28

You need to GROUP BY . Simplified example

WITH [CPCounting] AS(
        select 1 as [CarparkNo], 100 [CountingCategoryNo]  , 'aa' [CountingCategory]
        union all
        select 1 as [CarparkNo], 200 [CountingCategoryNo]  , 'bb' [CountingCategory]
        )
SELECT [CarparkNo]
    ,(SELECT [CountingCategoryNo]
        ,[CountingCategory]
      FROM [CPCounting] c2 
      WHERE c2.[CarparkNo] = c1.[CarparkNo] 
      FOR XML PATH('CountingCategory'), TYPE
      ) 
    FROM [CPCounting] c1
    GROUP BY [CarparkNo]    
    FOR XML PATH('Carpark'), ROOT('Carparks'), TYPE;

Returns

<Carparks>
    <Carpark>
        <CarparkNo>1</CarparkNo>
        <CountingCategory>
            <CountingCategoryNo>100</CountingCategoryNo>
            <CountingCategory>aa</CountingCategory>
        </CountingCategory>
        <CountingCategory>
            <CountingCategoryNo>200</CountingCategoryNo>
            <CountingCategory>bb</CountingCategory>
        </CountingCategory>
    </Carpark>
</Carparks>

Adding `GROUP BY` and the `WHERE` gives me `Column 'PARK_DB.dbo.CPCounting.CarparkAbbr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` — Bennett Blodinger, Aug 25 '17 at 18:40
But adding the `CarparkAbbr` and `CarparkDesig` to the `GROUP BY` works as needed! Thank you! — Bennett Blodinger, Aug 25 '17 at 18:42

SQL XML grouping

1 Answers1