Monday, January 14, 2013

how xml path concatenation trick works


select * from
(select  temp.data + ''  from
 (
select  'aa' joindata , null data
union select 'ba' joindata , 'a' data
union select 'bb' joindata , 'a' data
union select 'bb' joindata , 'b' data
union select 'bc' joindata , 'b' data
 ) temp
where temp.data is not null
FOR XML PATH('')
) as results(result)

returns aabb

why?since in the sql the column is thus temp.data + ''  designated - sql server does not know how to tag the data and therefore leaves it empty


No comments:

Post a Comment