Search code examples
sqlstringt-sqlaggregate-functionsstring-concatenation

tsql for xml string concat - How it works

I have the following code which works well:

    STUFF(  ( 
                        select 
                            char(13)+'Item '+i.item+' : '--+char(13) +i.item_descr
                        from @itemlines i
                        where i.customer=main.customer
                        FOR XML PATH(''), TYPE
                    ).value('.','varchar(max)')
                ,1,1, '')

What is the .value() thing? Something like a...select method? What does it do? Any reference links will be appreciated too!

Solution

  • FOR XML will return an XML datatype; the .value(...,...) pulls out the XML value and converts it to the datatype defined. In your case, everything in the root node ('.') converted to varchar(max)

    For some blogs/links look at Aaron Bertrand's post or Adam Machanic's also watch out for STRING_AGG a new function in SQL2017