Ufdjrw

How to reduce predictors the right way for a logistic regression model

What's the name of the logical fallacy where a debater extends a statement far beyond the original statement to make it true?

Quoting Keynes in a lecture

Does Doodling or Improvising on the Piano Have Any Benefits?

Is there a distance limit for minecart tracks?

Ways of geometrical multiplication

Has the laser at Magurele, Romania reached a tenth of the Sun's power?

Grepping string, but include all non-blank lines following each grep match

How do I fix the group tension caused by my character stealing and possibly killing without provocation?

How can I safely use "Thalidomide" in my novel while respecting the trademark?

Mimic lecturing on blackboard, facing audience

Would a primitive species be able to learn English from reading books alone?

Sigmoid with a slope but no asymptotes?

Can you identify this lizard-like creature I observed in the UK?

Why does the Persian emissary display a string of crowned skulls?

Integral Notations in Quantum Mechanics

Is there a reason to prefer HFS+ over APFS for disk images in High Sierra and/or Mojave?

Why can't the Brexit deadlock in the UK parliament be solved with a plurality vote?

How to preserve electronics (computers, iPads and phones) for hundreds of years

How do I prevent inappropriate ads from appearing in my game?

How much do grades matter for a future academia position?

How do I tell my boss that I'm quitting in 15 days (a colleague left this week)

Do people actually use the word "kaputt" in conversation?

Visualizing the difference curve in a 2D plot?

How to escape ampersand in MS SQL

What does <![CDATA[]]> in XML mean?How can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?Parameterize an SQL IN clauseInserting multiple rows in a single SQL query?How do I escape a single quote in SQL Server?How do I UPDATE from a SELECT in SQL Server?

1

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.

sql sql-server

share|improve this question

edited Jun 25 '14 at 15:38

Micro

asked Jun 25 '14 at 14:45

MicroMicro

5,74565684

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  As it's XML I would assume it wants & - worth a try anyway
  
  – James
  Jun 25 '14 at 15:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Try select 'a&z' col1 for XML path('x')
  
  – Bogdan Sahlean
  Jun 25 '14 at 15:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It does like & but I was wondering if there was a way to not use that.
  
  – Micro
  Jun 25 '14 at 15:48
  

  
  
  
  

add a comment | 

1

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.

sql sql-server

share|improve this question

edited Jun 25 '14 at 15:38

Micro

asked Jun 25 '14 at 14:45

MicroMicro

5,74565684

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  As it's XML I would assume it wants & - worth a try anyway
  
  – James
  Jun 25 '14 at 15:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Try select 'a&z' col1 for XML path('x')
  
  – Bogdan Sahlean
  Jun 25 '14 at 15:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It does like & but I was wondering if there was a way to not use that.
  
  – Micro
  Jun 25 '14 at 15:48
  

  
  
  
  

add a comment | 

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.

sql sql-server

share|improve this question

edited Jun 25 '14 at 15:38

Micro

asked Jun 25 '14 at 14:45

MicroMicro

5,74565684

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

/&
&
&&

sql sql-server

sql sql-server

share|improve this question

edited Jun 25 '14 at 15:38

Micro

asked Jun 25 '14 at 14:45

MicroMicro

5,74565684

share|improve this question

edited Jun 25 '14 at 15:38

Micro

asked Jun 25 '14 at 14:45

MicroMicro

5,74565684

asked Jun 25 '14 at 14:45

MicroMicro

5,74565684

asked Jun 25 '14 at 14:45

MicroMicro

5,74565684

4 Answers
4

active

oldest

votes

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

share|improve this answer

answered Jun 25 '14 at 14:47

Matas VaitkeviciusMatas Vaitkevicius

33.9k16167176

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

0

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

share|improve this answer

answered Sep 29 '16 at 0:01

Gabriel MolterGabriel Molter

134

add a comment | 

0

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.

share|improve this answer

edited Mar 7 at 3:23

answered Mar 7 at 3:18

user2366842user2366842

1,1981323

add a comment | 

-1

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

share|improve this answer

answered Jun 25 '14 at 15:01

mohan111mohan111

6,17611733

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  this isn't really practical for my situation...
  
  – Micro
  Jun 25 '14 at 15:38
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f24411836%2fhow-to-escape-ampersand-in-ms-sql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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

share|improve this answer

answered Jun 25 '14 at 14:47

Matas VaitkeviciusMatas Vaitkevicius

33.9k16167176

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

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

share|improve this answer

answered Jun 25 '14 at 14:47

Matas VaitkeviciusMatas Vaitkevicius

33.9k16167176

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

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

share|improve this answer

answered Jun 25 '14 at 14:47

Matas VaitkeviciusMatas Vaitkevicius

33.9k16167176

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

share|improve this answer

answered Jun 25 '14 at 14:47

Matas VaitkeviciusMatas Vaitkevicius

33.9k16167176

answered Jun 25 '14 at 14:47

Matas VaitkeviciusMatas Vaitkevicius

33.9k16167176

answered Jun 25 '14 at 14:47

Matas VaitkeviciusMatas Vaitkevicius

33.9k16167176

0

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

share|improve this answer

answered Sep 29 '16 at 0:01

Gabriel MolterGabriel Molter

134

add a comment | 

0

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

share|improve this answer

answered Sep 29 '16 at 0:01

Gabriel MolterGabriel Molter

134

add a comment | 

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

share|improve this answer

answered Sep 29 '16 at 0:01

Gabriel MolterGabriel Molter

134

share|improve this answer

answered Sep 29 '16 at 0:01

Gabriel MolterGabriel Molter

134

answered Sep 29 '16 at 0:01

Gabriel MolterGabriel Molter

134

answered Sep 29 '16 at 0:01

Gabriel MolterGabriel Molter

134

0

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.

share|improve this answer

edited Mar 7 at 3:23

answered Mar 7 at 3:18

user2366842user2366842

1,1981323

add a comment | 

0

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.

share|improve this answer

edited Mar 7 at 3:23

answered Mar 7 at 3:18

user2366842user2366842

1,1981323

add a comment | 

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.

share|improve this answer

edited Mar 7 at 3:23

answered Mar 7 at 3:18

user2366842user2366842

1,1981323

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

share|improve this answer

edited Mar 7 at 3:23

answered Mar 7 at 3:18

user2366842user2366842

1,1981323

answered Mar 7 at 3:18

user2366842user2366842

1,1981323

answered Mar 7 at 3:18

user2366842user2366842

1,1981323

-1

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

share|improve this answer

answered Jun 25 '14 at 15:01

mohan111mohan111

6,17611733

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  this isn't really practical for my situation...
  
  – Micro
  Jun 25 '14 at 15:38
  

  
  
  
  

add a comment | 

-1

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

share|improve this answer

answered Jun 25 '14 at 15:01

mohan111mohan111

6,17611733

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  this isn't really practical for my situation...
  
  – Micro
  Jun 25 '14 at 15:38
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Jun 25 '14 at 15:01

mohan111mohan111

6,17611733

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

share|improve this answer

answered Jun 25 '14 at 15:01

mohan111mohan111

6,17611733

answered Jun 25 '14 at 15:01

mohan111mohan111

6,17611733

answered Jun 25 '14 at 15:01

mohan111mohan111

6,17611733