Solucionado
Reparei que a ordem em que as colunas é colocada no SELECT afeta diretamente o agrupamento.
E também se não especificarmos uma cláusula order by adequada o resultado fica desorganizado. No exemplo, se acrescentarmos a cláusula ORDER BY Category.name,SubCategory.name,Product.name o resultado é bem diferente.
Olá, Flavio, tudo bem?
Isso mesmo Flavio, a ordem que informamos as colunas no SELECT afetam diretamente o resultado a ser retornado.
Se utilizamos a cláusula ORDER BY informando os camposCategory.name, SubCategory.name e Product.name, o resultado será ordenado primeiramente pela coluna Category.name, em seguida pela coluna SubCategory.name, e por ultimo pela coluna Product.name, caso contrario, o resultado não será ordenado.
Foi muito legal você ter compartilhado aqui com a gente as suas observações sobre FOR XML AUTO e sobre a utilização da cláusula ORDER BY para obtermos um resultado ordenado.
Qualquer dúvida é só falar e bons estudos!