Encode xml column values as xml in sql server

Question

I have a method that is taking vales directly from the database, building a string of xml and then writing the xml to a file.

This is fine until I get special characters eg "'", "<", "&" etc.

Does anyone know of something in Sql Server that would allow me to encode the values as i select them; for example;

select encode(service_status) from myTable

Thanks.

Answer 1

Use FOR XML clause.

It can build XML from multiple values automatically:

WITH  q AS (
        SELECT  'Barnes & Noble' AS shop
        UNION ALL
        SELECT  'Marks & Spencer'
        )
SELECT  *
FROM    q
FOR XML AUTO, TYPE

---
<q shop="Barnes &amp; Noble" /><q shop="Marx &amp; Spencer" />

If you just want to encode an existing value, use:

SELECT  'Barnes & Noble'
FOR XML PATH('')

---
Barnes & Noble

Answer 2

If you are using SQL Server version 2005/2008, then you are in luck, as you can create your own ENCODE function using CLR Functions.

A really good article can be found here.

Answer 3

If the calling application is building what you return into XML, then it is up to the calling application to encode the data. If you want to return XML from SQL Server, then it would be up to you, and the "FOR XML" answer by Quassnoi is correct.

Answer 4

If you have >= sql 2005, I think it may be easiest to stuff your value into an xml element and then pull it back out. This will entitize anything that needs encoding.

declare @x xml, @str varchar(8000), @encStr varchar(8000)
set @x = '<a/>'
set @str = '><&'

set @x.modify(
    'insert text{sql:variable("@str")}
    as first into (/a)[1]')

set @encStr = CAST(@x.query('/a/text()') as varchar(8000))
select @encStr
--returns: &gt;&lt;&amp;