If you’re trying to use a common table expression (CTE) that produces XML in SQL Server, but you’re getting error 4121, which reads something like “Cannot find either column “EmployeeData” or the user-defined function or aggregate “EmployeeData.query”, or the name is ambiguous” it might be that you’re trying to query the XML in the outer query, but your CTE isn’t outputting the XML as an actual xml type.

The FOR XML result must be an xml type in order to process the result on the server.

To fix this issue, make sure the CTE outputs the result using the xml data type.

Example of Error

Here’s an example of code that produces the error:

WITH EmployeesXML (EmployeeData) AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR XML PATH('Employee')
)
SELECT EmployeeData.query('/Employee/FirstName')
FROM EmployeesXML;

Output:

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "EmployeeData" or the user-defined function or aggregate "EmployeeData.query", or the name is ambiguous.

Here, I ‘m trying to use the query() function to extract a value from the XML document produced by my CTE. The problem is that my CTE didn’t output its result using the xml type, and so I got an error.

Solution

To fix this issue, we must change the CTE so that it outputs its result using the xml data type.

Here’s an example of fixing the CTE:

WITH EmployeesXML (EmployeeData) AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR XML PATH('Employee'), TYPE
)
SELECT EmployeeData.query('/Employee/FirstName')
FROM EmployeesXML;

Here, all I did was add , TYPE to the CTE query and it resolved the issue.

The TYPE directive is used to return the results of a FOR XML query as an xml type. Without using this, the XML document would be returned as a string using the nvarchar(max) type.

So it’s certainly possible to use the CTE without the TYPE directive. As mentioned, this would return the XML document as a string. We could then take that string and do whatever we needed. But if we want to process the result of the FOR XML query on the server, then we’d need to use the TYPE directive.

See Microsoft’s documentation for more information.