Use ampersand in CAST in SQL

sql sql-server xml casting ampersand
edosoft picture edosoft · Jul 13, 2009 · Viewed 11.4k times · Source

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

John Saunders picture John Saunders · Jul 13, 2009

It's not valid XML. Use &amp;:

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

Related questions

How can I query a value in SQL Server XML column

I have following XML stored in a XML column (called Roles) in a SQL Server database. <root> <role>Alpha</role> <role>Beta</role> <role>Gamma</role> &…

Read More
Use a LIKE statement on SQL Server XML Datatype

If you have a varchar field you can easily do SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%' to see if that column contains a certain string. How do you do that for XML Type? I have the following …

Read More
INSERT XML into SQL Server 2008 database

Hello I'm trying to insert some XML data into a table on SQL Server 2008. However I keep getting thrown this error; XML parsing: line 1, character 39, unable to switch the encoding The database column filemeta uses the XML datatype, and I've …

Read More