SQL xml column group and count element data

I need help grouping and counting elements from an xml data type column in a SQL Server table. The column data looks like the following:

<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 output would look like this:

Category  Count
A         1
B         3
C         2

I've figured 开发者_JAVA百科out how to get the Categories for each row but they are all stuck together like this:

ABBBCC

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 like B.value('(Category)[1]', 'varchar(50)') cannot be a part of a GROUP BY clause directly, the idea is to stick the code to grab the Category from the XML into a Common Table Expression (CTE) and select and group from that.