The quest for 0x0B

Question

I get this error when reading some data from an SQL column then converting it to XML:

"System.InvalidOperationException: There is an error in XML document (182, 16). ---> System.Xml.XmlException: ' ', hexadecimal value 0x0B, is an invalid character."

Fair enough, maybe the data is malformed. Except, how can I find the culprit row?

SELECT * from Mytable where Column like '%' + char(0x0B)+'%'

returns empty.

(obviously I attempted all %+char , char, char+% combinations, just in case)

Answer 1

Have you tried nchar(0x0B) instead of char(0x0B) yet? As it sounds like your dodgy character is Unicode.

Answer 2

Character 0x0B cannot be used in an XML document (see the list of valid XML characters here.) Please consider migrating invalid XML characters like this to valid XML (e.g. the sequence ).

Answer 3

Finally found it !

The .NET XML serializer was escaping the invalid character when serializing it, but then it was un-escaping it before de-serialization.

So I had to search for the escaped &#xB to find the un-escaped 0x0B ... seriously not funny guys!

So this:

  SELECT * from Mytable where Column like '%' + '&#xB' + '%'

Will actually find this:

<?xml version="1.0"?>
      <Hashtable><key>313_other_10</key><value>&#xB</value></Hashtable>

And while this looks like valid XML it will throw an invalid character exception when :

    XmlSerializer xs = new XmlSerializer(Type.GetType(Hashtable));
    StringReader stringReader = new StringReader(xml);
    obj = xs.Deserialize(stringReader);

Many thanks to people who jumped in to help! It was unvaluable help!