Mfcttrf

When I run the following

select t.type
 from (values ('Green'),('Blue'),('Red')) as t(type)
 for xml path('')

I receive this output

<type>Green</type>
<type>Blue</type>
<type>Red</type>

If I run the following

select t.type + '/'
 from (values ('Green'),('Blue'),('Red')) as t(type)
 for xml path('')

I receive this output

Green/Blue/Red/

Why does adding the concatenation in the select lead to the removal of the type tags and output on one line in the xml file? Running SQL Server 2012.

XML is bonkers

When you add the concatenated string, you lose the "path element".

For example if you do this:

SELECT t.type + '/' AS type
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML PATH('');

SELECT t.type + '/' 
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML PATH('type');

You get this back:

<type>Green/</type>
<type>Blue/</type>
<type>Red/</type>

The column name or alias acts as the path element.

Some other examples that might help

Using RAW, ELEMENTS

SELECT t.type + '/'
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, ELEMENTS;

SELECT t.type + '/' AS type
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, ELEMENTS;

In the first example, you get the generic "row" element name, but in the second you get row/type.

When using RAW, TYPE:

SELECT t.type + '/' AS type
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, TYPE;

SELECT t.type + '/'
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, TYPE;

The first query returns valid-ish XML, the second throws an error because the path element lacks an identifier.

Using AUTO, the table alias and column name turns into the path:

SELECT type + '/' AS type
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

SELECT type 
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

But without an alias, you get a similar error:

SELECT type + '/'
FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

I'd gin up an example with FOR XML EXPLICIT but it would be irresponsible for me to start drinking right now.