When using values of multiple types, SQL Server will always use Data Type Precedence (Transact-SQL). As you can see, INT is higher in the list than VARCHAR, so INT takes precendence. We both know that 'b' and 'd' aren't integers, so the conversion fails.

When using your for xml path, all the values are returning in a single column and a column CANNOT have fields of different types. Therefore Precedence applies. YOu're data would actually look like this:

v

---

1

b

3

d

Considering that 1 and 3 are of datatype INT, and 'b' and 'd' are VARCHARs, that table can't exist.

This, instead, would work for you:

DROP TABLE tmp.foo

CREATE TABLE tmp.foo (

id INT IDENTITY(1, 1) NOT NULL,

a TINYINT,

b VARCHAR(1),

c TINYINT,

d VARCHAR(1)

PRIMARY KEY (id)

);

-- single row so NULLs have no bearing on the error

INSERT INTO tmp.foo (a, b, c, d) VALUES (1, 'b', 3, 'd');

SELECT

id,

a,b,c,d,

bar = STUFF(

(

SELECT '-' + v

FROM (VALUES (CAST(a AS VARCHAR(MAX))), (CAST(b AS VARCHAR(MAX))), (CAST( c AS VARCHAR(MAX))), (CAST(d AS VARCHAR(MAX)))) AS v (v)

FOR XML PATH (''), TYPE

).value('.[1]', 'varchar(max)'),

1, 1, ''

)

FROM tmp.foo

ORDER BY id;