How to handle blank values in FOR XML PATH/AUTO - SQL Server?

Question

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>

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.

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>

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

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.

Answer 1

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?

Answer 2

You could do the following...up to you if you feel you need to TRIM or not;

CREATE TABLE #temp(field CHAR(2))

INSERT INTO #temp
SELECT NULL

SELECT field=COALESCE(NULLIF(RTRIM(LTRIM(field)), ''), '')
FROM #temp
FOR XML PATH('')

DROP TABLE #temp