Storing and conerting data from DB to XML-safe

I am using SQL 2005 for a DB and ColdFusion for an Application language. Is there a way in SQL that it can convert all fields I specify as XML-safe text?

For example, if the database stored: “Hello, my name is Aaron, what’s yours?”

I could always use ColdFusion’s XMLFormat() function (I’d just have to use it everywhere I reference data pulled from the DB, which is kind of taxing) to ensure that it was converted to:

“Hello, my name is Aaron, what's yours?”

So, is there a way SQL 2005 can do this conversion for me before I pass the data off to the application language?

Probably the quickest method would be to write your own XML entity encoder using the Replace method:

Replace(Replace(Replace(Replace(Replace(columnName,‘&’,‘&’),‘<’, ‘<’),‘>’, ‘>’),‘"’, ‘"’), ‘’‘’, ‘'’)

Just replace columnName with your column and use the above in your Select statements. I haven’t tested the above but it should be pretty close to what you need.

With SQL 2005, you can actually SELECT XML straight from the DB. Check out FOR XML AUTO to start with.

On the application layer, you probably want to look into serialization over hand encoding. Xml is not a string even if it can be expressed as one.