title: How to escape ampersand in MS SQL
body: <p>I have a table named tblCandy with an XML field named CandySpecs.  When I try to add a value containing an ampersand (&amp;) I get the error:</p>
  
  <pre><code>UPDATE tblCandy SET OrigOtherData.modify ('insert &lt;BrandName&gt;M&amp;Ms&lt;/BrandName&gt; as first into (CandySpecs/Table)[1]') WHERE RecordID = 1
  
  
  Msg 2282, Level 16, State 1, Line 1
  XQuery [tblCandy.CandySpecs.modify()]: Invalid entity reference
  </code></pre>
  
  <p>I’ve tried various escape sequences with no luck:</p>
  
  <pre><code>/&amp;
  \&amp;
  &amp;&amp;
  </code></pre>
  
  <p>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.</p>