TSQL XML column SELECT with an aggregate and GROUP BY clause

I needed to select a XML element value from my XML column using TSQL, but had a problem as my SELECT contained a COUNT aggregate and then a GROUP BY at the end. This is my query before introducing the XML SELECT:

SELECT 'JobLevels' AS ParentID, ID, Name, COUNT(L1.JobLevelID) AS ProductCount
FROM pc.CatJobLevel A1 INNER JOIN pc.CatProductJobLevel L1 ON A1.ID = L1.JobLevelID
GROUP BY ID, Name

Here is the resulting output:



ParentID	ID	Name	ProductCount
JobLevels	1	Entry-level	2
JobLevels	2	Executive	4
JobLevels	3	General Population	4

Now I wanted to bring back a localized Name value so I added a new XML column called Localisation and filled in the following XML for each row changing each Text value:

<Localisation>
<Columns>
<Column culture="ru" name="Name">
<Text>
?????????? ??????
</Text>
</Column>
</Columns>
</Localisation>


In order to bring back the correct XML element value I created this function so I can reuse it:

CREATE FUNCTION [GetLocalisationColumn]
( 
 @XmlColumn  XML -- The xml column
 ,@ColumnName NVARCHAR(100) -- The name of the column
 ,@Culture  NVARCHAR(10) -- The culture name
)
RETURNS NVARCHAR(MAX)
AS
BEGIN 
 DECLARE @Result NVARCHAR(MAX)

 SET @Result = CONVERT(NVARCHAR(MAX), @XmlColumn.query('/Localisation/Columns/Column[@name = sql:variable("@ColumnName") and @culture = sql:variable("@Culture")]/Text/text()'))
 
 RETURN @Result

END

My problem

When trying to call my function in the SELECT I received an error. Here is my new SQL statement and the error:

SELECT 'JobLevels' AS ParentID
 ,ID
 ,Name
 ,GetLocalisationColumn(Localisation, 'Name', 'ru') AS LocalisedName
 ,COUNT(L1.JobLevelID) AS ProductCount
FROM pc.CatJobLevel A1 INNER JOIN pc.CatProductJobLevel L1 ON A1.ID = L1.JobLevelID
GROUP BY ID, Name

Column 'CatJobLevel.Localisation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried to convert the XML column so it could be included in the GROUP BY but still got the same error:

SELECT 'JobLevels' AS ParentID
 ,ID
 ,Name
 ,pc.GetLocalisationColumn(Localisation, 'Name', 'ru') AS LocalisedName
 ,COUNT(L1.JobLevelID) AS ProductCount
FROM pc.CatJobLevel A1 INNER JOIN pc.CatProductJobLevel L1 ON A1.ID = L1.JobLevelID
GROUP BY ID, Name, CONVERT(NVARCHAR(MAX), Localisation)

My solution

I use a sub-query to join my localised results to and this works:

SELECT 'JobLevels' AS ParentID
 ,A1.ID
 ,A1.Name
 ,A11.Name AS LocalisedName
 ,COUNT(L1.JobLevelID) AS ProductCount
FROM pc.CatJobLevel A1 INNER JOIN pc.CatProductJobLevel L1 ON A1.ID = L1.JobLevelID
INNER JOIN (
 SELECT ID ,CONVERT(NVARCHAR(100), pc.GetLocalisationColumn(Localisation, 'Name', 'ru')) AS Name 
 FROM pc.CatJobLevel) AS A11 ON A1.ID = A11.ID
GROUP BY A1.ID, A1.Name, A11.Name

So the output is:

ParentID	ID	Name	LocalisedName	ProductCount
JobLevels	1	Entry-level	Начального уровня	2
JobLevels	2	Executive	исполнительный	4
JobLevels	3	General Population	Генеральный населения	4

Hope this helps anyone out there!