Change CheckCharacters on XmlReader generated by SqlCommand.ExecuteXmlReader

Question

If I try to change CheckCharacters the following way it, reader.Settings.CheckCharacters is still true. How am I supposed to do it?

using (var reader_org = command.ExecuteXmlReader())
{
    var settings = new XmlReaderSettings { CheckCharacters = false, ConformanceLevel = ConformanceLevel.Auto };
    var reader = XmlReader.Create(reader_org, settings);
    reader.Read();
}

According to the documentation it's supposed to work:

"Add features to an existing XML reader. The Create method can accept another XmlReader object. The underlying XmlReader object can be a user-defined reader, a XmlTextReader object, or another XmlReader instance that you want to add additional features to."

"it doesn't work" what doesn't work, what happens? What data do you have that you are trying to read? If you are (as I guess) using `FOR XML` in SQL Server, the data must already be valid XML, so what are you trying to do — Charlieface, Aug 18 '22 at 12:16
SQL will return invalid XML if you have invalid characters in a string, for example 0x1F — Kvasi, Aug 18 '22 at 12:31
Using the `, TYPE` directive, it automatically throws an error on invalid XML https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d55fba485f24ce5627995c146456eb24. Either way, what you are trying to do won't work, as the underlying `reader_org` has alreayd thrown an error. You can only *add* features, not remove. And why don't you consider actually generating valid XML, such as by encoding it as Base64? — Charlieface, Aug 18 '22 at 12:34

score 0 · Answer 1 · answered Aug 18 '22 at 20:03

It appears you are using FOR XML in SQL Server to generate certain types of XML that are not actually valid values, because they contain restricted characters, and is therefore not valid XML.

SQL Server will quite rightly not allow you to generate such XML if you use the , TYPE directive. But if you do not use that, it generates the XML as a string, and does not validate invalid characters. See also this article.

Ideally, you would use Base64 or similar to encode this. But assuming for whatever reason you don't want to do this, then the reason your current code does not work is that the underlying reader_ord XML reader already has CheckCharacters = true so will throw an exception.

Instead you need to create your own XML reader from the string. Since FOR XML without , TYPE also splits up large XML blobs into separate rows, you also need to concatenate them all first.

var sb = new StringBuilder();
using (var reader = command.ExecuteReader())
{
    while (reader.Read())   // read all rows
    {
        sb.Append(reader.GetString(0));
    }
}

var settings = new XmlReaderSettings { CheckCharacters = false, ConformanceLevel = ConformanceLevel.Auto };
using (var xmlReader = XmlReader.Create(sb.ToString(), settings))
{
    // do stuff with reader here
}

There are more performant ways to do that: for example you could create your own Stream out of sequential reader.GetStream results, but that is significantly more complex.

Change CheckCharacters on XmlReader generated by SqlCommand.ExecuteXmlReader

1 Answers1