sql server - sql studio can't see special characters in XML

Question

For some reason Visual Studio does not show me special characters when I query for an XML field. Maybe I stored them wrong? These are smart quotes

Here's the query:

select CustomFields from TABLE where ID=422567 FOR XML PATH('')

When I copy/paste into notepad++ I see this:

What are STS and CCH?

Answer 1

Strings are - as you surely know - just chains of numbers. What they mean and how they are interpreted is depending on codepages, encodings, little or big endian ...

Just have a look on this

SELECT 'test' AS NormalText

       --non printable characters
       --they are things like backspace, carriage return
      ,CHAR(0x6) AS ACK    --DEC   7
      ,CHAR(0x7) AS BEL    --DEC   9
      ,CHAR(0x1A) AS CR    --DEC  13
      ,CHAR(0x1B) AS ESC   --DEC  27

      --printable characters from 0x21 (DEC 33) up to 0x7F (DEC 127) - (almost) not depending on encoding
      ,CHAR(0x41) AS BigA  --DEC  65
      ,CHAR(0x7E) AS Tilde --DEC 126

      --extended - from 0x80 (DEC 128) - very much depending on encoding!
      ,CHAR(0x93) AS STS   --DEC 147
      ,CHAR(0x94) AS CCH   --DEC 148
      ,CHAR(0x93) + 'test' + CHAR(0x94) AS Mixed
FOR XML PATH('')

This will produce this

<NormalText>test</NormalText>
<ACK>&#x6;</ACK>
<BEL>&#x7;</BEL>
<CR>&#x1A;</CR>
<ESC>&#x1B;</ESC>
<BigA>A</BigA>
<Tilde>~</Tilde>
<STS>“</STS>
<CCH>”</CCH>
<Mixed>“test”</Mixed>

As you see, there are characters which must be encoded, as there is no character expression for them, others are displayed with their corresponding "picture".

With codes above DEC 127 you enter dangerous terrain. The same string can produce quite different output depending on where you read it.

The "STS" and "CCH" Notepad shows to you, are taken from C1 Controls and Latin-1 Supplement.

This, and the written Smart qoutes in your example point to this. In order to allow smart qoutes there are general characters for start and end which are "replaced" with the fitting opening and closing qoutation marks.

Finally XML in SQL Server is always UTF16. Have a look at this feff0093 and feff0094. These are the signs UTF16 binds to 0x93 and 0x94. My small example shows this clearly...

So the question is: Why does your picture not show the “ and the ” ?

I don't know... The select you put in the first line would not "produce" this XML, it rather takes existing XML out of a column "CustomFields". I'm fairly sure, that this is not a "real" XML-column...