Geek asks and answers

Storing a special character (e.g. &) in XML data format

If i do

Declare @t table(Email xml)
Declare @email varchar(100) = 'xxx&xx@monop.com'
Insert into @t  
select '<Emails> <Email>' + @email +'</Email></Emails>'
select * From @t

I will get the expected error

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

One solution that I found almost everywhere (including SO) is that it replace '&' with '&amp;works

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

Output

<Emails><Email>xxx&amp;xx@monop.com</Email></Emails>

However, I tried to use the CData approach (just another way to approach the problem)

Declare @t table(Email xml)
Declare @email varchar(100) = 'xxx&xx@monop.com'
Insert into @t  
Select CAST('<![CDATA[Emails> <Email>' + @email + '</Email> </Emails]]>' AS XML)
select * From @t

When did I get the bottom output

Emails&gt; &lt;Email&gt;xxx&amp;xx@monop.com&lt;/Email&gt; &lt;/Emails

What I'm trying to achieve is to store the data as it is, that is, the desired result should be

<Emails><Email>xxx&xx@monop.com</Email></Emails>

Is it possible?

I know that the replace function will not work if some other special character that xml cannot understand is passed as input to it, for example. '& L;' in which case we must replace it again ...

thank

+5
3

PCDATA, CDATA, CDATA.

+3

XML, XML SQL Server.

:

/* Create xml and add a variable to it */
DECLARE 
    @xml xml = '<Emails />',
    @email varchar(100) = 'xxx&xx@monop.com';

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

SELECT @xml;

/* Output:
<Emails>
  <Email>xxx&amp;xx@monop.com</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&xx@monop.com */

.

Roman

+2

, ([N]VARCHAR) - XML. FOR XML ( ) XML (&→ &amp;; < → &lt; ..), , , XML methods ( (...) (...)) XML (&amp; → &; &lt; → < ..) ([N]VARCHAR).

SET ANSI_WARNINGS ON;
GO

DECLARE @t TABLE(Email xml)
DECLARE @email VARCHAR(100) = '<Coco&Jambo>@monop.com'

DECLARE @x XML;
--Encoding
SET @x =
(
    SELECT @email
    FOR XML PATH('Email'), ROOT('Emails')
)

INSERT INTO @t  
VALUES (@x);

--Decoding
SELECT  t.Email AS EncodedEmail_XML,
        t.Email.value('(/Emails/Email)[1]', 'VARCHAR(100)') AS DecodedEmail_VARCHAR100
FROM    @t t

:

EncodedEmail_XML                                                 DecodedEmail_VARCHAR100
---------------------------------------------------------------- -----------------------
<Emails><Email>&lt;Coco&amp;Jambo&gt;@monop.com</Email></Emails> <Coco&Jambo>@monop.com
+1

More articles:


All Articles