weedbreath
New Member
If I do\[code\]Declare @t table(Email xml)Declare @email varchar(100) = 'xxx&[email protected]'Insert into @t select '<Emails> <Email>' + @email +'</Email></Emails>'select * From @t\[/code\]I will get expected errorMsg 9411, Level 16, State 1, Line 8XML parsing: line 1, character 27, semicolon expectedOne solution which I found almost everywhere(including SO) is to \[code\]replace '&' with '&\[/code\] and it works\[code\]Insert into @t select CAST('<Emails><Email>' + REPLACE(@email, '&', '&') + '</Email></Emails>' AS XML)\[/code\]Output\[code\]<Emails><Email>xxx&[email protected]</Email></Emails>\[/code\]However, I was trying with CData approach (just another way to approach the problem)\[code\]Declare @t table(Email xml)Declare @email varchar(100) = 'xxx&[email protected]'Insert into @t Select CAST('<![CDATA[Emails> <Email>' + @email + '</Email> </Emails]]>' AS XML)select * From @t\[/code\]When I got the below output\[code\]Emails> <Email>xxx&[email protected]</Email> </Emails\[/code\]What I am trying to achieve is to store the data as it is i.e. the desired output should be\[code\]<Emails><Email>xxx&[email protected]</Email></Emails>\[/code\]Is it at all possible?I know that the replace function will fail if any other special character that xml fails to understand will be passed as an input to it e.g. '<' i which case again we need to replace it...Thanks