or
sql-server
kevinnwhat imported from SE
When I run the following

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

I receive this output

``` xml
<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

``` none
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.
Top Answer
Erik Darling
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:

``` xml
<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.
for xml path('') output

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.