问题描述
有没有人有任何见解或遇到过这种 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>  </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>  </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(''))),' ','')
DROP TABLE #temp
想听听您是否还有其他建议?
would like to hear if there is any other suggestion?
这篇关于如何处理 FOR XML PATH/AUTO - SQL Server 中的空白值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！