In my earlier post, I wrote about how one can use XML to convert table to string SQL SERVER – Comma Separated Values (CSV) from Table Column. The same article is also published on channel 9 SQLAuthority News – Featured on Channel 9. One of the very interesting points that was discussed on show was about the usage of function SUBSTRING. I found the following point very valid: SUBSTRING usage limits the length of the XML to be used.
I have re-written the same function with function STUFF, and it removes any limit imposed on the script.
USE AdventureWorks GO -- Check Table Column SELECT [Name] FROM HumanResources.Shift GO -- Get CSV values SELECT STUFF( (SELECT ',' + s.Name FROM HumanResources.Shift s ORDER BY s.Name FOR XML PATH('')),1,1,'') AS CSV GO
Do let me know your thoughts on the same.
Reference : Pinal Dave (http://blog.SQLAuthority.com)