問題描述
有沒有人有任何見解或遇到過這種 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 中的空白值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！