SQL 2008: Insert XML value with backslash-doublequote combo

Question

I need to insert some xml 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.

There's a similar question http://stackoverflow.com/questions/650821/how-do-i-escape-double-quotes-in-attributes-in-an-xml-string-in-t-sql, the answer is to use `"` — thomasswilliams, Apr 13 '11 at 23:45
@Pedro - Presumably XML `select CAST('' as XML)` gives `XML parsing: line 1, character 37, whitespace expected`. — Martin Smith, Apr 13 '11 at 23:47
@thomaswilliams - I looked and didn't see that one. Probably because I didn't include the term XML in my search. — Dan Bailiff, Apr 14 '11 at 14:47

Mikael Eriksson · Accepted Answer · 2011-04-14T05:35:23.090

2

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"/>

edited Apr 14 '11 at 05:35

answered Apr 14 '11 at 05:26

Mikael Eriksson

Ah, I didn't realize that the problem was the XML and not the SQL. Many thanks. – Dan Bailiff Apr 14 '11 at 14:45

score 0 · Answer 2 · answered Apr 13 '11 at 23:44

0

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

answered Apr 13 '11 at 23:44

Ron Harlev

2 Answers2