Storing special character(e.g. &) in XML datatype

Question

If I do

Declare @t table(Email xml)
Declare @email varchar(100) = 'xxx&[email protected]'
Insert into @t  
select '<Emails> <Email>' + @email +'</Email></Emails>'
select * From @t

I will get expected error

Msg 9411, Level 16, State 1, Line 8 XML parsing: line 1, character 27, semicolon expected

One solution which I found almost everywhere(including SO) is to replace '&' with '& and it works

Insert into @t  
select CAST('<Emails><Email>' + REPLACE(@email, '&', '&amp;') + '</Email></Emails>' AS XML)

Output

<Emails><Email>xxx&amp;[email protected]</Email></Emails>

However, I was trying with CData approach (just another way to approach the problem)

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

When I got the below output

Emails> <Email>xxx&[email protected]</Email> </Emails

What I am trying to achieve is to store the data as it is i.e. the desired output should be

<Emails><Email>xxx&[email protected]</Email></Emails>

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

Solution 1

Tags are PCDATA, not CDATA, so don't put them in the CDATA section.

Solution 2

When you work with XML you should use XML-related features of SQL Server.

For example:

/* Create xml and add a variable to it */
DECLARE 
    @xml xml = '<Emails />',
    @email varchar(100) = 'xxx&[email protected]';

SET @xml.modify ('insert (
    element Email {sql:variable("@email")}
) into (/Emails)[1]');

SELECT @xml;

/* Output:
<Emails>
  <Email>xxx&amp;[email protected]</Email>
</Emails>
*/

/* Extract value from xml */

DECLARE @email_out varchar(200);

SET @email_out = @xml.value ('(/Emails/Email)[1]', 'varchar (200)');

SELECT @email_out; /* Returns xxx&[email protected] */

Good luck

Roman

Comments

[Admin]

did u mean Select CAST('<![PCDATA[Emails> <Email>' + @email + '</Email> </Emails]]>' AS XML) . it fails XML parsing: line 1, character 4, incorrect CDATA section syntax

[Ignacio Vazquez-Abrams]

No. Leave the tags alone. Put only the text in a CDATA section.

[Admin]

i think u mean Select CAST('<![CDATA[' + @email + ']]>' AS XML)

[Ignacio Vazquez-Abrams]

You'll need the tags as well of course. But they go outside the CDATA section.

[Ignacio Vazquez-Abrams]

Seriously, we just went through this. Tags go outside the CDATA section.

[Admin]

Select CAST('<Emails><Email><![CDATA[' + + @email + ']]></Email></Emails>' AS XML) gives out as <Emails><Email>xxx&amp;[email protected]</Email></Emails> but not the one specified in the question.. i mean there is no way to store '&' directly in the xml column?

[Admin]

one last question... why cannot we store special character in xml column?

[Ignacio Vazquez-Abrams]

That's like asking why we can't use periods to end each word. Because it's used for other things. Which is why we encode it.