for xml path('') output

Question

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.

Answer 1

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.