How to escape ampersand in MS SQL

Question

I have a table named tblCandy with an XML field named CandySpecs. When I try to add a value containing an ampersand (&) I get the error:

UPDATE tblCandy SET OrigOtherData.modify ('insert <BrandName>M&Ms</BrandName> as first into (CandySpecs/Table)[1]') WHERE RecordID = 1


Msg 2282, Level 16, State 1, Line 1
XQuery [tblCandy.CandySpecs.modify()]: Invalid entity reference

I’ve tried various escape sequences with no luck:

/&
\&
&&

There is a lot of guidance out there on this issue and I’m wondering if there is one best way to address this problem.

As it's XML I would assume it wants `&` - worth a try anyway — James, Jun 25 '14 at 15:12
It does like `&` but I was wondering if there was a way to not use that. — Micro, Jun 25 '14 at 15:48

user2366842 · Answer 1 · 2019-03-07T03:23:33.273

Here's a much better way to deal with this:

UPDATE tblCandy SET OrigOtherData.modify ('insert <BrandName><![CDATA[M&Ms]]></BrandName> as first into (CandySpecs/Table)[1]') WHERE RecordID = 1

Explanation: the CDATA tag tells the XML to ignore character markup for this block of data.

Related StackOverflow question (not strictly a dupe, but would be worth reading if you're not familiar with this): What does <![CDATA[]]> in XML mean?

This will bypass not only the &, but also other potentially breaking pieces of data such as < and > that could potentially exist within the data you're dealing with.

score 0 · Answer 2 · answered Jun 25 '14 at 14:47

0

Special symbols in SQL server are being escaped with \

in your example statement would look following:

UPDATE tblCandy SET OrigOtherData.modify ('insert <BrandName>M\&Ms</BrandName> as first into (CandySpecs/Table)[1]') WHERE RecordID = 1

answered Jun 25 '14 at 14:47

Matas Vaitkevicius

58,075
31
238
265

Tried both back and forward slash and that didn't work. SSMS didn't like it. – Micro Jun 25 '14 at 15:05

score 0 · Answer 3 · answered Sep 29 '16 at 00:01

0

Using & instead of just &. I found the answer on this article: http://www.techrepublic.com/article/beware-of-the-ampersand-when-using-xml/

answered Sep 29 '16 at 00:01

Gabriel Molter

333
2
10

score -1 · Answer 4 · answered Jun 25 '14 at 15:01

SET NOCOUNT ON
GO

CREATE TABLE tblCandy ( Id INT, Brandname XML )
GO

INSERT INTO tblCandy VALUES ( 1, '<Brandname >test</Brandname >' )
GO

SELECT 'before', * FROM tblCandy

UPDATE tblCandy 
SET Brandname.modify('replace value of (//Brandname/text())[1]
with string("as first into")')
WHERE Id = 1

SELECT 'After', * FROM tblCandy
GO

DROP TABLE tblCandy
GO

How to escape ampersand in MS SQL

4 Answers4