Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-02-20 : 08:05:31
|
| Martin writes "I have a varchar column in a table which contains a series of text descriptions, which contain many of the characters such as &, < and >.The contents of this table is later turned into very simple XML with string concatenation later on in a stored procedure. All the code for this application lives inside stored procedures - there is no 'caller' application which would allow me to use SELECT...FOR XML to have SQL perform the XML formation for me.What I'm looking for is an way to escape the characters that XML doesn't like, maybe via a user-defined function.Thus "Gin & Tonic" should become "Gin & Tonic" for example in the database column.I was fully expecting an XMLEncode type function to already exist within SQL Server, but after much websearching & manual reading, I can't see any likely candidates.Any help would be appreciated.Martin" |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-02-20 : 08:35:03
|
| If the column's value will end up as character data in XML, only ampersand and less-than need to be escaped. If it will end up as an attribute value, then so do greater-than, and either apostrophe or double-quote (depending on which is being used to enclose the attribute value).Assuming the former case, all you need to do is useREPLACE(REPLACE(column_name, '&', '&'), '<', '<')If your XML's character encoding is not the same as that used by your varchar column (as determined by its COLLATE), then you may need to recode other characters.Edit: Gah! This forum software eats entity references somewhere... |
 |
|
|
|
|
|
|
|
Making a string XML-friendly ?