如何处理 FOR XML PATH/AUTO - SQL Server 中的空白值?

2021-10-01 00:00:00 xml special-characters sql-server

有没有人有任何见解或遇到过这种 SQL Server 行为，当空白值转换为数据类型char"或具有char"作为数据类型的列时 - 使用 For XML PATH 处理，它返回带有"特殊字符编码的 XML 结果，
当用 varchar 转换相同的空白值时 - 它返回空标签.我想要的结果是空标签.

Does anybody have any insight or encountered with this SQL Server behavior, when blank value is converted into data type "char" or column which have "char" as data type - processed using For XML PATH, it returned XML result with "" special characters encoding for space,
When same blank value converted with varchar - it returns empty tag. My desired result is empty tag.

SELECT field=CONVERT(CHAR(10),'')
FOR XML PATH(''), ELEMENTS
--RESULT: <field>          &#x20;</field>

SELECT field=CONVERT(VARCHAR(10),'')
FOR XML PATH(''), ELEMENTS
--RESULT: <field></field>

我认为的解释是，当我使用 char 时，它插入了 10 个空格.而 ASCII Hex20 或 Decimal 32 - 是空格的代码.

The explanation in my view is when I'm using char it is inserting 10 spaces. And ASCII Hex20 or Decimal 32 - is a code for space.

它可以通过使用 varchar 而不是 char 作为数据类型来处理.但就我而言，我正在从数据库中定义的表中读取字段值:

It can be handled by using varchar instead of char as data type. But in my case I am reading field value from table defined in database:

--Example:
CREATE TABLE #temp(field CHAR(2))

INSERT INTO #temp
SELECT NULL

SELECT field=ISNULL(field,'')
FROM #temp
FOR XML PATH('')

DROP TABLE #temp

--RESULT: <field>  &#x20;</field>
--Desired Result:<field></field>

SQL 中最优雅的方式/或可能性是什么?

What is a most elegant way/or possibilities in SQL?

PS:我有一个 50 列的结果集.我想在数据库级别处理这个而不改变类型.真正的问题是我的 Web 服务在 XML 中遇到此字符时会引发潜在的危险值错误.

PS: I have a result set of 50 columns. I would like to handle this at database level without changing types. Real problem is my web service when encounter this character in XML throws a potentially dangerous value error.

推荐答案

我用于实现预期结果的解决方案是，一次性投射整个结果 -

Solution I used for desired result is, casting a whole result at once - 

CREATE TABLE #temp(field CHAR(2))

INSERT INTO #temp
SELECT NULL

--Adding a cast to XML Result to convert it into varchar & then use replace.
SELECT REPLACE(CONVERT(VARCHAR(max),(SELECT field=ISNULL(field,'')
FROM #temp
FOR XML PATH(''))),'&#x20;','')

DROP TABLE #temp

想听听您是否还有其他建议?

would like to hear if there is any other suggestion?

相关文章