ExecuteXmlReader uses XmlReaderSettings that are incompatible with SQL Server
- Dominant language
- C#
- Stars
- 989
- Forks
- 340
- Avg merge
- 4d 19h
- Merged PRs (30d)
- 72
Description
### Describe the bug
I am trying to read a data from a system that stores SMS message bodies. The field is `NVARCHAR(MAX)` and each message is terminated with an EOT (end of transmission) character. The EOT character has a decimal value of 4.
When reading this as XML generated by SQL Server's `FOR XML RAW` the XML is rendered with `` in it, like so:
```
```
This is not a valid character, and `XmlReader` disallows it by default:
```
System.Xml.XmlException: '', hexadecimal value 0x04, is an invalid character. Line 1, position 233415.
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)
at System.Xml.XmlTextReaderImpl.ParseNumericCharRefInline(Int32 startPos, Boolean expand, StringBuilder internalSubsetBuilder, Int32& charCount, EntityType& entityType)
at System.Xml.XmlTextReaderImpl.ParseNumericCharRefAsync(Boolean expand, StringBuilder internalSubsetBuilder)
at System.Xml.XmlTextReaderImpl.HandleEntityReferenceAsync(Boolean isInAttributeValue, EntityExpandType expandType)
at System.Xml.XmlTextReaderImpl.ParseAttributeValueSlowAsync(Int32 curPos, Char quoteChar, NodeData attr)
at System.Xml.XmlTextReaderImpl.ParseAttributesAsync()
at System.Xml.AsyncHelper.ReturnTrueTaskWhenFinishCoreAsync(Task task)
at System.Xml.XmlWriter.WriteNodeAsync_CallAsyncReader(XmlReader reader, Boolean defattr)
at ...
```
However SQL Server generates this invalid XML, and I have an expectation that `Microsoft.Data.SqlClient` would be able to consume it. This is quite achievable:
https://github.com/dotnet/SqlClient/blob/d4f69fdc1b302a4039a618ae61538a4f98be3b0b/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlTypes/SqlTypeWorkarounds.cs#L25-L27
These `XmlReaderSettings` need `CheckCharacters = false`.
### To reproduce
```c#
using Microsoft.Data.SqlClient;
const string connectionString = "...";
await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
await using var command = connection.CreateCommand();
command.CommandText = "SELECT N'Sorry running late' + CHAR(4) AS SMS_RECEIVED FOR XML RAW";
using var reader = await command.ExecuteXmlReaderAsync();
while (await reader.ReadAsync())
;
```
### Expected behavior
`Microsoft.Data.SqlClient` is able to parse all the XML SQL Server generates.
### Further technical details
Microsoft.Data.SqlClient version: 4
.NET target: .NET 6
SQL Server version: 2019
Operating system: Windows 11
**Additional context**
I can try my hand a pull request if you agree that `Microsoft.Data.SqlClient` ought to be able to interpret the XML generated by SQL Server, and that `CheckCharacters = false` is the solution.
Contributor guide
Assessment
This issue has not been assessed yet.