CODEWITHSUNDEEP
sqlxmlsql-server-2008
Dan Bailiff
Dan Bailiff

Reputation: 1581

SQL 2008: Insert XML value with backslash-doublequote combo

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.

Upvotes: 1

Views: 6717

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Escape the " with &quot; 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"/>

Upvotes: 2

Ron Harlev
Ron Harlev

Reputation: 16713

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

Upvotes: 0

Related Questions