Skip to main content


Combine Rows with FOR XML PATH

We can use FOR XML PATH to combine rows.

 For example, have the following data:


And I want to combine the value of column SRC_ID_SEED from all rows to a single row as:


We can achieve this by using FOR XML PATH():


stuff((select ‘,’+cast(src_id_seed as varchar(20))

         from DATA_SOURCES

         FOR XML PATH ( ” )),

         – Function stuff used to remove the first character (,)

         1,1,”)as Code


But if you have special characters in value then you need to use Type directive to create the XML data type.

 For example: if you want the result like following


You need to use the sql with type:

 select (select ‘<‘+cast(src_id_seed as varchar(20))+’>’


FOR XML PATH ( ” ),TYPE).value(‘.’,’varchar(max)’)as code


If not use Type then the result will be:


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us