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!

LINQ to XML Enum Extension Method Converter

Below are easy to use extension methods to extract a generic enum from an XML attribute using LINQ to XML:

    
public static class ExtensionMethods
    {
        public static string SafeAttribute(this XElement element, string key)
        {
            var attribute = element.Attribute(key);
            return attribute != null ? element.Attribute(key).Value : "";
        }

        public static T SafeAttribute<T>(this XElement element, string key)
        {
            var attribute = SafeAttribute(element, key);
            return !string.IsNullOrEmpty(attribute) ? (T)Enum.Parse(typeof(T), attribute) : default(T);
        }
    }

Any this is how you would call it:

var xml = XDocument.Load(filePath);
var e = xml.Element("Item").SafeAttribute<MyEnum>("myKey");

LINQ to XML

Below is an example of how I used LINQ to XML to read an XML string and search for nodes that match a criteria within the tree. It also shows how I order by an attribute.

The XML:

The Code:

Generating XSD from and XML file

If you want to generate an XML schema (*.xsd) file from an existing XML file you can use the xsd.exe tool.

Go to your Visual Studio command line and type in the following:

xsd.exe C:\file.xml /outputdir:C:\

Done!

Navigating XElement

The XMl looks like this:

I first create a navigator with an XPath expression to get the nodes set I want to work with:

XElement root = XElement.Load("data.xml");
var answers = root.CreateNavigator().Select("/Category/Subject/Question/Answer");

Then I loop through each answer...

while (answers.MoveNext())
{

Here I convert the underlying object to an Element to get the values:

var details = answers.Current.UnderlyingObject as XElement;

Now I can access the elements I want:

var val = details.Element("Description").Value;

}

Best way to write XML in C#

This is my opinion the way to create XML within c# - LINQ to XML:

var root =

new XElement("content",

new XAttribute("id", node.Id),

new XAttribute("url", node.NiceUrl),

new XAttribute("title", property != null ? property.Value : node.Name)

);