Use ampersand in CAST in SQL

Question

The following code snippet on SQL Server 2005 failed with the ampersand '&' error:

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

Does anyone know a workaround?

A more detailed explanation, I need to update some data in an XML column, and I use hack type lookup and replace, discarding the XML value in varchar, doing the XML column replace and update with this cast.

Answer 1

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

The literal ampersand inside the tag is XMLnot allowed by the standard XML, and such a document will not be parsed using any analyzer XML.

An XMLSerializer()will issue an ampersand HTML-encoded.

:

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());
        }
    }
}

:

<?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>

& &.

Answer 2

XML. &:

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

Answer 3

XML, .

, backtrack , :

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

- :

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

, , , :

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

Answer 4

XML SQL , .

XML XML, :

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

Answer 5

, & . , - , . - (, & "), ( , , , , " ).

, & HTML-, : &. &lt; <, > > " ".