XML Reader on SQL Table returning invalid XML

Question

I have SQL table that has a varchar(8) column that occasionally has binary data in it. (0x01, 0x02, etc...). (Changing the format or the content of the column isn't an option.)

When I go into the SQL Server 2005 Management Studio and run the query:

select * 
 from mytable 
 where clientID = 431620
 for xml auto

I get useful results. Notice how proc_counts is encoded:

<mytable clientID="431620" recno="19014235" pdate="2008-03-04T00:00:00"
   proc_counts="&#x1;&#x1;&#x2;&#x1;" otherstuff="foobar" 
   date="2008-02-17T00:00:00"/>

Perfectly valid XML, AFAIK. Now when I actually write C# code to read this row, I'm getting an exception throw during ReadOuterXml:

SqlCommand cmd = new SqlCommand("select * from testing xml auto", connection);
using (XmlReader xrd = cmd.ExecuteXmlReader())
{
    xrd.Read();
    while (xrd.ReadState != ReadState.EndOfFile)
    {
        string s = xrd.ReadOuterXml();
        records.Add(s);
    }
}

This throws: XmlException was unhandled. '', hexadecimal value 0x01 is an invalid character. I want the XML from above, but don't have sufficient Google-fu to figure out why I'm not getting it. Suggestions?

---

To create a table with this kind of data, this Transact SQL code works in SSMS:

create table testing
(clientid int, proc_counts varchar(8));
insert into testing values (1, 'normal');
insert into testing values (2, char(65) + char(1) + char(65));
select * from testing for xml auto;

---

Update: Post-mortem and workaround

Dommer's probably right, that it's the Normalization property in the XmlTextReader that's giving me problems. The thing is (as you can see from the comments) I found it pretty much impossible to go from a (SqlCommand).ExecuteXmlReader() to anything that will let me go near the Normalization property of an XmlTextReader. Oftentimes the Microsoft documentation on this was contradictory or just plain wrong.

So I settled on a workaround. If I simply use an SqlDataReader to soak up the output everything is fine. The XML looks perfect and parses quite nicely.

StringBuilder sb = new StringBuilder();
using(SqlDataReader dr = cmd.ExecuteReader())
{
    while(rdr.Read())
        sb.Append((string)rdr[0]);
}

Answer 1

The exception is telling you the truth. SQL Server apparently permits the display of invalid XML. Try this:

select *  from mytable  where clientID = 431620 for xml auto BINARY BASE64

Answer 2

It's to do with the XmlTextReader.Normalization property. This property is set to false when you explicitly create an XmlTextReader, so the "invalid" characters are decoded. When the XmlTextReader is created implicitly, Normalization is set to true.

The property is discussed here:

http://msdn.microsoft.com/en-us/library/system.xml.xmltextreader.normalization.aspx

If you do the processing "manually" - i.e. create an XmlTextReader - I think you'll avoid the error.

UPDATE:

Changes in the more recent versions of the framework mean that "XmlReaderSettings.CheckCharacters = false" may be the way to go in ASP.NET 2.0+. An XmlReaderSettings object can be passed to XmlReader.Create.