declare @X xml = ' <item ID = "0"/> <item ID = "1"/> <item/> <item/>'; select I.X.value('@ID', 'int') from @X.nodes('/item') as I(X);
----------- 0 1 NULL NULL
The top branch shreds the XML to four rows and the bottom branch fetches the value for the attribute
What strikes me as odd is the number of rows returned from the Stream Aggregate operator. The 2 rows that comes from the Filter is the
ID attribute from the first and second
item nodes in the XML. The Stream Aggregate returns four rows, one for each input row, effectively turning the Inner Join to an Outer Join.
Is this something that Stream Aggregate does in other circumstances as well or is it just something odd going on when doing XML queries?
I can not see any hints in the XML version of the query plan that this Stream Aggregate should behave any differently than any other Stream Aggregate I have noticed before.