sql server - FOR XML PATH(''): Escaping "special" characters -

This code basically describes the characters in the same string in the second string based on the condition in a string and it Runs for everyone rows in the table

When I run this (simplified version):.

  @R Four (40) Declaration @U Four Announcement (40) Set @ R = '! Abcdefghijklmnopqrstuvwxyz @ # $% ^ & amp; @TestTable table (RowID integer identification (1,1) primary key, vague varchar (500)) @TestTable value ('+ μt) * () _ +' + 4 (181) set @ u = reverse (@R) $ ZW! * Μsu + yt! + S $ xy ') Enter the @TestTable value ('% * !! xμpxu! '(@) Into the @TestTable value (' pxpμnxrμu + yμs put in% $ T '); CodeValues With the number (SUBSTRING (@R, number, 1) R, ASCII (SUBSTRING (@U, number, 1)) where the number is UA where number & lt; = LEN (@R) Select T. ROD, (Select number '+ Combination from CR based on number N interiors ASCII CodeValues ​​(SUBSTRING (t.Unreadable, n.Number, 1)) = c.UA Where n.Number & lt; = LEN (t.Unreadable) for XML path ('')) @TestTable Readable from T  

I get the following:

  Rudid Readable ----------- -------- --- ---------------------------- A and # x20; Simple & amp; # x20; Translation 2 Hello & amp; # X20; world 3 wow & # x20; you & amp; # x20; ran & amp; # x20; this  

but it needs:

  Rudid Readable ---------- - ---------------------------------- ----- 1 A simple translation 2 Hello world 3 You have gone this  

, except REPLACE () Is there any way to show the expressed position correctly? It also happens on line breaks in my actual code.

Can it be rewritten in a better way? I originally used the XML path ('') to just add one line values ​​together.

After

The XML you received is correct, this is XML , not a text, and XML is readable by XML parser via XML. Special characters are escaping exactly, as they should be whatever the client module thinks that XML should be parsed as XML, it should not be written as text, and then it will be displayed properly.

Update:

If this is not clear, then all you need to do in your query is to treat XML as text and XML as text, XML Do not mix in the form of text, ie:

 ; With the number of numbers and LT; = LN (@R), with codeValues ​​AS (SELECT Number, SUBSTRING (@ R, number, 1) ASR, ASCII (SBSTERING (U, NO, 1)), XMLValuas (SELECT t.ROWID, number n INNER, from the "SELECT" + cr codec as well as on ASCII (cybestering (T. UNABabel, N. number, 1)) = CUU, where N. number and LTX = LN (T UNABible) XML Path ('', type) for @TestTableTable as readable) Select X ROID, is readable from x.readable.value ('.', 'VARCHAR (8000)') XmlValues ​​AS x