XML reserved chars

consider this short T-SQL snippet: it fails because instruction set @m = '<boom' contains an illegal char:

declare @m nvarchar(255)
set @m = '<boom'
declare @msgs nvarchar(max)
set @msgs = '<test1>'+@m+'</test1>'
declare @x xml
set @x = @msgs

Anyone knows a way to automatically or manually convert '<boom' into '<boom' when I assign to an xml datatype
something like that: set @msgs = '<test1>'+PREPARE_FOR_XML(@m)+'</test1>'

TIA

Answer this question

XML reserved chars

Braneloc
Thanks for your reply, but what I'm asking is a way to do the job without the need to know wich chars to replace... the same that happens whan you put a text in the XmlDomDocument.
jwize
Use replace

declare @m nvarchar(255)
set @m = '<boom'
declare @msgs nvarchar(max)
set @msgs = '<test1>'+replace(replace(@m,'<','<'),'>','>')+'</test1>'
declare @x xml
set @x = @msgs

select @x

Denis the SQL Menace
http://sqlservercode.blogspot.com/
Uwe Keim
Then use CDATA

declare @m nvarchar(255)
set @m = '<boom'
declare @msgs nvarchar(max)
set @msgs = '<test1>'+ '<![CDATA[' + @m+ ']]>' + '</test1>'
declare @x xml
set @x = @msgs

select @x

Denis the SQL Menace
http://sqlservercode.blogspot.com/
vitich
I've tried the CDATA and I'm surprised of what happens: if you run the following statement

declare @m nvarchar(255)
set @m = '<boom'
declare @msgs nvarchar(max)
set @msgs = '<test1>'+ '<![CDATA[' + @m +']]>'+'</test1>'
declare @x xml
set @x = @msgs
select @x

you get the following result:
<test1><boom</test1>

Seems that SQL converts the CDATA section into his own equivalent representation and removes the CDATA section.

Thanks.
XML reserved chars

Answer this question

XML reserved chars

Hot Topic

SQL Server

XML reserved chars

XML reserved chars

Braneloc

jwize

Uwe Keim

vitich