Xml SQL column data and count element data

Question

I need to group and count help items from an XML data type column in a SQL Server table. The column data is as follows:

<Books count="6">
  <Book id="1">
    <Category>A</Category>
  </Book>
  <Book id="2">
    <Category>B</Category>
  </Book>
  <Book id="3">
    <Category>B</Category>
  </Book>
  <Book id="4">
    <Category>B</Category>
  </Book>
  <Book id="5">
    <Category>C</Category>
  </Book>
  <Book id="6">
    <Category>C</Category>
  </Book>
</Books>

The result will look like this:

Category  Count
A         1
B         3
C         2

I figured out how to get Categories for each row, but they are all glued together like this:
ABBBCC

Answer 1

You can try something like this:

DECLARE @Input XML 
SET @Input = '<Books count="6">
  <Book id="1">
    <Category>A</Category>
  </Book>
  <Book id="2">
    <Category>B</Category>
  </Book>
  <Book id="3">
    <Category>B</Category>
  </Book>
  <Book id="4">
    <Category>B</Category>
  </Book>
  <Book id="5">
    <Category>C</Category>
  </Book>
  <Book id="6">
    <Category>C</Category>
  </Book>
</Books>'

;WITH CatValues AS
(
    SELECT
        B.value('(Category)[1]', 'varchar(50)') 'Category'
    FROM
        @Input.nodes('/Books/Book') as B(B)
)
SELECT
    Category, COUNT(*)
FROM
    CatValues
GROUP BY
    Category

Since an XML operation, such as B.value('(Category)[1]', 'varchar(50)'), cannot be part of the sentence GROUP BYdirectly, the idea is to stick to the code in order to capture Categoryfrom XML into the Common Table Expression (CTE) and select groups from it.