sql studio 看不到 XML 中的特殊字符

2021-09-18 00:00:00 visual-studio xml sql sql-server

出于某种原因，当我查询 XML 字段时，Visual Studio 没有向我显示特殊字符.也许我存储错误?这些是明智的报价

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

这是查询:

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

当我复制/粘贴到记事本++时，我看到:

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

什么是 STS 和 CCH?

What are STS and CCH?

推荐答案

字符串 - 正如您肯定知道的 - 只是数字链.它们的含义和解释方式取决于代码页、编码、小端或大端...

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 ...

看看这个

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('')

这将产生这个

<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".

如果代码高于 DEC 127，您将进入危险地带.相同的字符串可能会产生完全不同的输出，具体取决于您阅读的位置.

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

向您展示的STS"和CCH"记事本取自 C1 控制和 Latin-1 补充.

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

This 和您示例中的书面 Smart qoutes 指向 this.为了允许smart qoutes，start 和end 的通用字符被替换"为合适的打开和关闭qoutation 标记.

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.

最后，SQL Server 中的 XML 始终是 UTF16.看看这个 feff0093 和 feff0094.这些是 UTF16 绑定到 0x93 和 0x94 的标志.我的小例子清楚地表明了这一点......

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 " ?

我不知道...您放在第一行的 select 不会生成"这个 XML，而是从CustomFields"列中取出现有的 XML.我相当确定，这不是真正的"XML 列...

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...

相关文章