This is where the FOR XML PATH('') and STUFF() come into play. How do we do this?
Here is the syntax for what we want to accomplish and then we'll go through it a little more detailed.
Stuff syntax:
STUFF( select statement, start index, length, character expression )
For XML Path syntax:
FOR XML PATH(element) in our case we want to put two single quotes in the for xml path parentheses.
FOR XML PATH('')
To get the data into the comma delimited result we start with FOR XML PATH(''):
Note that if you want the XML out you would not use ('') at the end of FOR XML PATH. If you omit this then you will get an out put for that column like
<data ><row>, <row><data ..."
By adding the ('') you remove the XML wrapper elements. You can read more on MSDN Books Online under FOR XML PATH.
STUFF()
Notice that we started the string with a comma. You could end it with a comma if you choose to just changes how the next phase works. I choose to put it at the beginning and then trim it with STUFF().
Which brings us to cleanup by using STUFF(). By using stuff we can omit the leading comma from the result set giving us a cleaner output in case you want to parse it later. STUFF() is like using a range REPLACE(). Instead of replacing all instances of something we are only replacing a specified location or range of characters.
Lets look at the syntax and break it down:
- Syntax: STUFF(
, , , ) - start index tells us where to begin replacing characters with character string.
- length tells us how many characters are we going to be affecting.
- character string tells us what we are going to be replacing or "stuffing" in place of the affected characters. In our case we replaced only the leading comma giving us the follow result.
NOTE:You only need to use STUFF() if you want to trim the result. If you are satisfied with your result then you don't need to use this. I am only using it to trim the leading comma.
