xml constants do not have the N prefix

[Charlieface]

Bug description

When a constant of type xml (SQL Server) is used in a query, the prefix N is not being inserted before the single quotes, so the string is considered as non-Unicode, and therefore the database collation's codepage is applied. This causes a loss of data.

Although WHERE is not allowed on an xml column, such constants can still appear in UPDATE SET clauses, or in the SELECT.

Your code

The simplest repro is just a SELECT with an emoji in an XML string. In this case you can verify the string does have the correct SqlServerStringTypeMapping with the xml type, but the N prefix is not being generated.

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
					
public class Program
{
	public static void Main()
	{
		using var db = new MyDb();
		Console.WriteLine(
		    db.MyEntities
			    .Select(e => e.SomeXml ?? "<x Attr=\"😭\" />")
			    .ToQueryString());
	}
}

public class MyDb : DbContext
{
	public DbSet<MyEntity> MyEntities {get;set;} = null!;
	
    protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<MyEntity>().Property(p => p.SomeXml).HasColumnType("xml");
	}
	
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer("Data Source = .");
	}
}

public class MyEntity
{
	public int Id {get;set;}
	public string? SomeXml {get;set;}
}

dotnetfiddle

You can see the results are incorrect by looking at this db<>fiddle

SELECT COALESCE([m].[SomeXml], '<x Attr="😭" />')
FROM [MyEntities] AS [m]

outputs

<?xml version="1.0" encoding="utf-16"?>
<x Attr="??" />

instead of

<?xml version="1.0" encoding="utf-16"?>
<x Attr="😭" />

EF Core version

10.0.9

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 10

Operating system

No response

IDE

No response

[Charlieface]

It seems to me the bug is very simple: in SqlServerStringTypeMapping the GenerateNonNullSqlLiteral function relies on _isUtf16 as to whether the to output the N prefix. Which is set in the constructor by this line:

efcore/src/EFCore.SqlServer/Storage/Internal/SqlServerStringTypeMapping.cs

Line 113 in 5538cb2

_isUtf16 = parameters.Unicode && parameters.StoreType.StartsWith("n", StringComparison.OrdinalIgnoreCase);

So it's looking for the parameters to be IsUnicode and for the StoreType to begin with a n/N, which excludes xml.

Exactly why this variable even exists is unclear to me. I see no reason why we couldn't just rely on base.IsUnicode. Be that as it may, if you really wanted a separate variable it should have just been:

_isUtf16 = parameters.Unicode;

[AndriySvyryd]

The only issue with flipping to Unicode is that SQL Server rejects an nvarchar literal that contains an explicit XML prolog encoding declaration when it is converted to xml, because the declared encoding (e.g. utf-8) conflicts with the UTF-16 nvarchar string. The non-Unicode form is accepted. Verified on SQL Server 2025:
 

-- current (no N): OK
DECLARE @x xml = '<?xml version="1.0" encoding="utf-8"?><r>a</r>';   -- succeeds
 
-- proposed (with N): fails
DECLARE @y xml = N'<?xml version="1.0" encoding="utf-8"?><r>a</r>';
-- Msg 9402: XML parsing: line 1, character 38, unable to switch the encoding

 
So flipping xml to emit N literals would break queries that embed an xml string constant whose content begins with <?xml ... encoding="..."?> (a non-UTF‑16 encoding). Such prologs are common when an application round-trips XML that was originally produced elsewhere.

This would have to be documented as a breaking change

[Charlieface]

That's true, although by the same token an embedded prolog encoding="utf-16" doesn't work at the moment because of the incorrect encoding.

Options as I see it:

• Detect the prolog in GenerateNonNullSqlLiteral and conditionally output the N.
• Allow xml columns to use the Unicode attribute.
• Switch the prolog to utf-16. Transmitting XML as UTF-16 and marking it with a utf-8 prolog is itself a bug (ie by the user) so not as ridiculous as it sounds. SQL Server does this itself if you convert non-Unicode varchar to xml.

The confounding issue here is that SqlClient deals with parameters of SqlDbType.Xml very strangely. It only passes the parameter typed as xml if you give it a SqlXml value, for which you'd need to pass either a Stream or an XmlReader. For bare string it passes it as nvarchar(max), which already fails with a utf-8 prolog, but not with utf-16.

Perhaps we should always be passing in SqlXml as the parameter? Might need some work around getting an efficient custom Stream over a string without any copying.

[Charlieface]

Having said that, by the looks of the code in SqlParameter.CoercedValue it's just reading the Stream back into a string. So that's going to be inefficient. We might as well stick to string anyway, but passing it inside a SqlXml to get the correct type server-side.

Either way, xml columns are currently broken in both directions at the moment, if you have a prolog. Literals only work with utf-8 and parameters only work with utf-16.

[Charlieface]

A bit of testing seems to show that we can simply do

sqlParameter.Value = value;   // the string
sqlParameter.SqlDbType = SqlDbType.Xml;   // you MUST set it again after setting the value