The return type of the FOR XML PATH
expression is NVARCHAR(MAX)
, not XML
.
I was being confused by the fact that SSMS renders any column named XML_F52E2B61-18A1-11d1-B105-00805F49916B
as if it has the XML
data type. If I run the following command, I get a clickable XML result cell in SSMS:
SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml');
I was able to figure out the actual type by getting SQL Server to spit out a type-conversion error:
SELECT SQL_VARIANT_PROPERTY((SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')), 'BaseType');
Msg 206, Level 16, State 2, Line 1
Operand type clash: nvarchar(max) is incompatible with sql_variant
To fix the warning completely, simply add the TYPE
directive to the FOR XML
clause:
SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'), TYPE);
The warnings you're seeing most likely come from the sys.sysdepends view.
If you script it out using
EXEC sys.sp_helptext @objname = N'sys.sysdepends'
The definition has a bunch of converts and other nonsense going on.
CREATE VIEW sys.sysdepends AS
SELECT
id = object_id,
depid = referenced_major_id,
number = convert(smallint,
case when objectproperty(object_id, 'isprocedure') = 1 then 1 else column_id end),
depnumber = convert(smallint, referenced_minor_id),
status = convert(smallint, is_select_all * 2 + is_updated * 4 + is_selected * 8),
deptype = class,
depdbid = convert(smallint, 0),
depsiteid = convert(smallint, 0),
selall = is_select_all,
resultobj = is_updated,
readobj = is_selected
FROM sys.sql_dependencies
WHERE class < 2
UNION ALL
SELECT -- blobtype dependencies
id = object_id, depid = object_id,
number = convert(smallint, column_id), depnumber = convert(smallint, type_column_id),
status = convert(smallint, 0), deptype = sysconv(tinyint, 1),
depdbid = convert(smallint, 0), depsiteid = convert(smallint, 0),
selall = sysconv(bit, 0), resultobj = sysconv(bit, 0), readobj = sysconv(bit, 0)
FROM sys.fulltext_index_columns
WHERE type_column_id IS NOT NULL
sys.objects, on the other hand, is fairly straightforward.
CREATE VIEW sys.objects AS
SELECT name,
object_id,
principal_id,
schema_id,
parent_object_id,
type,
type_desc,
create_date,
modify_date,
is_ms_shipped,
is_published,
is_schema_published
FROM sys.objects$
The view definition for sys.sysdepends causes the same warnings when queried on its own.
SELECT *
FROM sys.sysdepends
In general, if you want to control datatypes and indexes and have some performance tuning ability when referencing system views or tables, your best bet is to dump them into a temp table first.
Best Answer
There's nothing to fix, really.
The message refers to this computed column in the
Sales.SalesOrderHeader
table:The warning is informational in this case, to be sure you're aware in case it was a problem, as discussed in this Q&A: Type conversion in expression may affect “CardinalityEstimate” - on a computed column?