Ran into an issue where we had non-ascii characters in a varchar(max) column. Due to these characters we couldn’t cast the document to an XML doc. When I tried, we would get:

XML parsing: line 49, character 20, illegal xml character

I took a look and saw the character was non-standard and not part of a node I needed anyway. I used the function below to clean out the bad characters and get an XML doc.

CREATE function [dbo].[RemoveSpecialChars] (@s varchar(max))
returns varchar(max)
AS
begin
if @s is null
return null
declare @s2 varchar(max)
set @s2 = ”
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin declare @c int set @c = ascii(substring(@s, @p, 1)) if @c = 32 OR @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 set @s2 = @s2 + char(@c) set @p = @p + 1 end if len(@s2) = 0 return null return @s2 end

Leave a Reply

Your email address will not be published. Required fields are marked *