1

Topic: Why the request about aggregation of lines with for xml so strange works?

Actually, the request text here:

Declare @t table (id int, id2 int, t varchar (255))
insert into @t
Values
(1, 1, ' t1 ');
(1, 1, ' t2 ');
(1, 1, ' t3 ');
(2, 2, ' t4 ');
(3, 2, ' t5 ');
(4, 1, ' t6 ')
Select Distinct a.id, c.t from @t a
Cross apply
(select ' / ' + b.t as [data ()]
from @t b
where a.id = b.id2
--for xml path (")
) c (t)

With  for xml path (") it fulfills is expected:

id t
1 /t1
1 /t2
1 /t3
1 /t6
2 /t4
2 /t5

And here with  - it is unexpected!

id t
1 /t1/t2/t3/t6
2 /t4/t5
3 NULL
4 NULL

Why???
It is played back both on 2008R2, and on 2016SP1.

2

Re: Why the request about aggregation of lines with for xml so strange works?

uaggster;
What exactly surprises you?

3

Re: Why the request about aggregation of lines with for xml so strange works?

uaggster;
If that, smoke towards aggregate queries without phrase GROUP BY....
Compare result of an output:

select max (id2) from @t where 1 = 2
select max (id2) from @t where 1 = 2>>> group by id <<<

In the first case you receive a resultant dial-up in single line with EMPTY VALUE.
In the second - EMPTY SET, not the content of any line.
You feel a difference?

4

Re: Why the request about aggregation of lines with for xml so strange works?

uaggster;
Well, if suddenly, you did not understand analogy for xml path (") is, as though,"analog"of a modular subquery without slander GROPU BY. That is, it always returns"single line"on an output, without dependence from falsehood or the validity of conditions of connection with the exterior table...
Another matter that this "single line" will or contain result of concatenation of all lines, satisfying to condition WHERE, or " value" if conditions WHERE it appears "false"

5

Re: Why the request about aggregation of lines with for xml so strange works?

  - Eh wrote:

uaggster;
What exactly surprises you?

Me surprises that (like) if cross apply did not return any line, means also a line to which there is a connection, and result of function (in this case - the subordinate ) - to sampling should not to get.
Like.

6

Re: Why the request about aggregation of lines with for xml so strange works?

  - Eh wrote:

uaggster;
Well, if suddenly, you did not understand analogy for xml path (") is, as though,"analog"of a modular subquery without slander GROPU BY. That is, it always returns"single line"on an output, without dependence from falsehood or the validity of conditions of connection with the exterior table...
Another matter that this "single line" will or contain result of concatenation of all lines, satisfying to condition WHERE, or " value" if conditions WHERE it appears "false"

And why it so???
Than such behavior is dictated?

7

Re: Why the request about aggregation of lines with for xml so strange works?

uaggster;
Once again I speak - do experiment with a modular subquery without GROUP BY. The result will be similar, because aggregate query (read with for xml) without group by - always returns exactly single line, without dependence from number of the lines which have got to handling...
Tangently apply - it at cross returns notations in total  lines for which the subquery appeared "nonblank set".
And so, you for xml does resultant  a subquery - "nonblank"... Therefore lines from the main table also are pushed out in total result...

8

Re: Why the request about aggregation of lines with for xml so strange works?

uaggster wrote:

And why it so???
Than such behavior is dictated?

the documentation and the ANSI SQL standard

9

Re: Why the request about aggregation of lines with for xml so strange works?

  - Eh wrote:

and so, you for xml does resultant  a subquery - "nonblank"... therefore lines from the main table also are pushed out in total result...

Italicized - certainly, clearly.
Not clear - selected fat.
Why for xml - does result  - nonblank?
I understand that it is dictated by the standard (if it could be written off on a bug, it would not be played back on different versions), I understand, where at it to look!