sql server 2005 - 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

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!