SQL 2008: Insert XML value with backslash-doublequote combo

I need to insert some xm开发者_StackOverflow中文版l into a SQL table column that looks like this:

<date format="ddd MMM dd HH:mm:ss \"UTC\" yyyy" />

SQL complains it is expecting whitespace after the double quote before the U.

INSERT INTO foo
(date)
VALUES ('<date format="ddd MMM dd HH:mm:ss \"UTC\" yyyy" />')

I've tried doubling the double quotes and the backslashes, but I get the same error.

Your XML is invalid. " is not allowed in attribute values when you enclose the value with ".

Escape the " with " like this

<date format="ddd MMM dd HH:mm:ss \&quot;UTC\&quot; yyyy" />

Or use ' to enclose the attribute value

<date format='ddd MMM dd HH:mm:ss \"UTC\" yyyy' />

The result in the XML column in SQL Server is the same no matter how you do it.

<date format="ddd MMM dd HH:mm:ss \&quot;UTC\&quot; yyyy"/>

Use SQL parameters to avoid any issues with the values you have to insert