問題描述
有沒有人有任何見解或遇到過這種 SQL Server 行為，當(dāng)空白值轉(zhuǎn)換為數(shù)據(jù)類型char"或具有char"作為數(shù)據(jù)類型的列時 - 使用 For XML PATH 處理，它返回帶有"特殊字符編碼的 XML 結(jié)果，
當(dāng)用 varchar 轉(zhuǎn)換相同的空白值時 - 它返回空標(biāo)簽.我想要的結(jié)果是空標(biāo)簽.
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>
我認(rèn)為的解釋是，當(dāng)我使用 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 作為數(shù)據(jù)類型來處理.但就我而言，我正在從數(shù)據(jù)庫中定義的表中讀取字段值:
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 中最優(yōu)雅的方式/或可能性是什么?
What is a most elegant way/or possibilities in SQL?
PS:我有一個 50 列的結(jié)果集.我想在數(shù)據(jù)庫級別處理這個而不改變類型.真正的問題是我的 Web 服務(wù)在 XML 中遇到此字符時會引發(fā)潛在的危險值錯誤.
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.
推薦答案
我用于實現(xiàn)預(yù)期結(jié)果的解決方案是，一次性投射整個結(jié)果 -
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?
這篇關(guān)于如何處理 FOR XML PATH/AUTO - SQL Server 中的空白值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！