none
XML Data Type - Illegal Characters RRS feed

  • Question

  • In my SQL app, I am converting a varchar(max) type, passed to a stored procedure, to an XML data type in the stored proc.

    I've noticed that if the contents of the varchar(max) string contains illegal XML characters (such as '&'), the conversion will fail. This doesn't surprise me, and I would expect that. My question is this. Is there any SQL function I can call, or some other method, that will convert all illegal XML characters to legal equivalents?

    Thanks - Amos.

    Monday, August 28, 2006 6:07 PM

Answers

  • There is no built-in function to entitized illegal XML chars.  Since SQL2005 do support CLR, you can develop your own built-in function using C# to do it.
    Friday, September 1, 2006 4:36 PM
  • Here's how I do it:

    create function encodeXml( @txt nvarchar(4000) )
    returns nvarchar(4000)
    as
    begin
      declare @xml nvarchar(4000)
      set @xml = replace(@txt, '&', '&')
      set @xml = replace(@xml, '<', '&lt;')
      set @xml = replace(@xml, '>', '&gt;')
      set @xml = replace(@xml, '"', '&quot;')
      set @xml = replace(@xml, '''', '&apos;')
      return @xml
    end


    -Gordon
    Friday, September 1, 2006 7:10 PM

All replies

  • There is no built-in function to entitized illegal XML chars.  Since SQL2005 do support CLR, you can develop your own built-in function using C# to do it.
    Friday, September 1, 2006 4:36 PM
  • Here's how I do it:

    create function encodeXml( @txt nvarchar(4000) )
    returns nvarchar(4000)
    as
    begin
      declare @xml nvarchar(4000)
      set @xml = replace(@txt, '&', '&amp;')
      set @xml = replace(@xml, '<', '&lt;')
      set @xml = replace(@xml, '>', '&gt;')
      set @xml = replace(@xml, '"', '&quot;')
      set @xml = replace(@xml, '''', '&apos;')
      return @xml
    end


    -Gordon
    Friday, September 1, 2006 7:10 PM