If you’re running a subquery that uses FOR XML to produce XML in SQL Server, but you’re getting error 258, which reads “Cannot call methods on nvarchar(max)” it might be that you’re trying to query the XML in the outer query, but your inner query 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 subquery outputs the result using the xml data type.

Example of Error

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

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

Output:

Msg 258, Level 15, State 1, Line 5
Cannot call methods on nvarchar(max).

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

Solution

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

Here’s an example of fixing the subquery:

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

Here, all I did was add , TYPE to the subquery 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 we could use the subquery without the TYPE directive, and this would return the XML document as a string. But if we wanted to process the result of the FOR XML query on the server (like using the query() function to extract a value), then we’d need to use the TYPE directive.

You may be getting the same error but in a different scenario, but hopefully this post helps.

See Microsoft’s documentation for more information.