For my generic data layer I’m using SQLXML 3.0SP3 to load XML into typed DataSets based on annotated XSD Schemas. For storing changed data I work with the SqlXmlAdapter managed class. When one of the columns in the table is of type ‘ntext’ or ‘text’ I run into trouble: when the value for this column contains a [ character you will always receive an exception, related to an empty update, because no row was affected.
The reason this happens is because the LIKE operator is used by the SQLXML
engine to find the correct row in the table together with an optimistic concurrency check. But with the [ character in the column value the SQL engine mistakenly assumes that this is used as the sequence to escape a character. So when you try to update a row that has a column called ‘csharpcode’ with the following value:
// Some sample code
public class Foo
it will never be updatable or deletable with the SqlXmlAdapter. In general
it seems a solution to specify an ESCAPE clause, but since SQLXML generates
the SQL statement you have no influence on this process.
What to do? The only solution, also suggested by Andrew Conrad, is to escape the [ characters before storing the values. This will not always be easy, as you can read in my next post on the biggest hack made by me ever (agreed, I haven’t done that much hacking in my life) ;).