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.
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.
