Escaping for the SQL adapter

Just came across this funny quirk:

I've been working on a BizTalk/SQL interface through the SQL Send adapter, where the stored procedure being called takes in XML as a parameter (we are using the XML datatype, but I'm pretty sure you could use a char/varchar type as well), and the sproc makes some OPENXML calls to parse the XML and do some insertions.

When you use the Add Adapter Metadata to create the schema that the adapter uses to call the stored procedure, each parameter is represented as an XML attribute. Since at least one of these parameters is a string with XML in it, the string needs to be properly escaped, because <'s are illegal in attribute values.

SQL supports taking in escaped XML, but there's one thing you should know, that I only discovered by trial and error: ampersands need to be doubly-escaped, to "$amp;amp". I'm not sure why this is; my best guess is that when the statement is executed, after characters are de-escaped, the ampersand is an illegal character within SQL. By double-escaping it, the character will be escaped (and subsequently ignored) again during the execution pass.

As far as I've been able to tell, simply using HttpUtility.HtmlEncode will do all the work you need, except for escaping the ampersand twice. Escape all ampersands with a String.Replace BEFORE calling HtmlEncode for the correct result.

Also, note that HtmlEncode does NOT escape single quotes (apos's). Usually, this doesn't lead to problems, even though single quotes are reserved in XML. I'm not up to snuff on whether attribute values are *supposed* to be surrounded with double quotes or single quotes, but I do know that BizTalk will accept messages either way, and the messages that BizTalk generates have double quotes around attribute values. If you have a value wrapped in double quotes, single quotes within that value won't hurt anything, but they will if the value is wrapped in single quotes. Just something to watch out for.