Use ampersand in CAST in SQL

Question

The following code snippet on SQL server 2005 fails on the ampersand '&':

select cast('<name>Spolsky & Atwood</name>' as xml)

Does anyone know a workaround?

Longer explanation, I need to update some data in an XML column, and I'm using a search & replace type hack by casting the XML value to a varchar, doing the replace and updating the XML column with this cast.

Answer 1

select cast('<name>Spolsky &amp; Atwood</name>' as xml)

A literal ampersand inside an XML tag is not allowed by the XML standard, and such a document will fail to parse by any XML parser.

An XMLSerializer() will output the ampersand HTML-encoded.

The following code:

using System.Xml.Serialization;

namespace xml
{
    public class MyData
    {
        public string name = "Spolsky & Atwood";
    }

    class Program
    {
        static void Main(string[] args)
        {
            new XmlSerializer(typeof(MyData)).Serialize(System.Console.Out, new MyData());
        }
    }
}

will output the following:

<?xml version="1.0" encoding="utf-8"?>
<MyData
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <name>Spolsky &amp; Atwood</name>
</MyData>

, with an & instead of &.