How to encode in XML on SQL Server

Question

I am generating XML as follows:

SET @xml = (
        SELECT 
                PersonalMessage AS [Personal_Message]
        FROM #TemporaryDB
        FOR XML PATH ('Order'), ROOT ('Orders'), ELEMENTS
        );

Next I add encoding info:

SELECT N'<?xml version="1.0" encoding="utf-8"?>' + Convert(nvarchar(max), @XML)

And when I save my XML with bcp I get something like this:

<?xml version="1.0" encoding="utf-8"?>
<Orders><Order>
<Personal_Message>Liebe X1 und X2,
"Wir wuenschten wir koennten heute mit euch zusammen sitzen. Das holen wir bald wieder nach."
Eure &amp; Ina </Personal_Message></Order></Orders>

But I want something like this:

<?xml version="1.0" encoding="utf-8"?>
<Orders><Order>
<Personal_Message>Liebe X1 und X2,&#13;&#13; &quot;Wir wuenschten wir koennten heute mit euch zusammen sitzen. Das holen wir bald wieder nach.&quot; &#13;&#13;Eure &amp; Ina </Personal_Message></Order></Orders>

So I want to change C/R to  and for example " to ".

Please, advise me. How can I do this?

can you include your bcp command here? what is the format in your bcp? — ughai
– ughai, Commented Apr 29, 2015 at 13:01
do you insert this xml back into a table and then use a bcp on that table? — ughai
– ughai, Commented Apr 29, 2015 at 13:14

Marcelo Vollbrecht · Accepted Answer · 2015-05-01 08:38:38Z

1

You might have to replace it

SET @xml = (
        SELECT 
                Replace(Replace(Replace(PersonalMessage,char(13),'&#13;'),char(10),''),'"','&quot;') AS [Personal_Message]
        FROM #TemporaryDB
        FOR XML PATH ('Order'), ROOT ('Orders'), ELEMENTS
        );

answered May 1, 2015 at 8:38

Marcelo Vollbrecht

314 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to encode in XML on SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related