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.

Solution 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 &.

Solution 2

It's not valid XML. Use &:

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

Solution 3

You'd need to XML escape the text, too.

So let's backtrack and assume you're building that string as:

SELECT '<name>' + MyColumn + '</name>' FROM MyTable

you'd want to do something more like:

SELECT '<name>' + REPLACE( MyColumn, '&', '&amp;' ) + '</name>' FROM MyTable

Of course, you probable should cater for the other entities thus:

SELECT '<name>' + REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( MyColumn, '&', '&amp;' ), '''', '&apos;' ), '"', '&quot;' ), '<', '&lt;' ), '>', '&gt;' ) + '</name>' FROM MyTable

Solution 4

When working with XML in SQL you're a lot safer using built-in functions instead of converting it manually.

The following code will build a proper SQL XML variable that looks like your desired output based on a raw string:

DECLARE @ExampleString nvarchar(40)
    , @ExampleXml xml

SELECT  @ExampleString = N'Spolsky & Atwood'

SELECT  @ExampleXml =
    (
        SELECT  'Spolsky & Atwood' AS 'name'
        FOR XML PATH (''), TYPE
    )

SELECT  @ExampleString , @ExampleXml

View more solutions

Comments

[Martin Liversage]

I believe your XML entity "&" has been replaced by "&" by Stack Overflow. Very tricky.

[Vilius Surblys]

Thinking about it, you could probably do this better playing about using the 'FOR XML' to generate the XML, and the relevant XPath to read the source, if it already is properly formatted XML - would need a better description of what you're trying to achieve with schema to really make a judgement

[edosoft]

Thanks. This does work, but puts the literal '&' in the xml. The original XML which I'm trying to hack, was written by XmlSerializer.Serialize(), which kept the ampersand as-is in the xml. I'd like to reproduce that (preferably without going to the whole serializing process again :)

[Martin Liversage]

It is OK in the code, but I (perhaps wrongly) assumed that you wanted the text to read "It's not valid XML. Use "&"".

[KM.]

@Martin Liversage & @John Saunders, yeah, I thought you wanted the verbose "&" in the text explainning the code...

[John Saunders]

@KM: You were right. I thought you were talking about the code. Please go ahead and fix the text.

[Martin Liversage]

If your XML contains unescaped &-characters is is basically invalid XML and you really should work on fixing that.

[Rich Andrews]

This is dead handly for converting a text field in the db into an XML output as well, bugged me for ages that