FOR XML PATH IN SQL

FOR XML PATH in sql is used to get comma separated values from tables where there is one to many relation between tables

FOR XML PATH in SQL

We use FOR XML PATH in sql to get comma separated values   from tables have one to many relation.

For example we have two tables tblCategory and tblProduct and one category can have multiple products. Now we want to get all products of a category in comma separated in single columns then we can use FOR XML PATH

tblCategory

CategoryID	CategoryName
1	Electronics
2	Apparels

tblProduct

ProductID	ProductName	Cost	CategoryID
1	Laptop	45000	1
2	LCD TV	40000	1
3	DVD Player	5000	1

Use this query

SELECT     CategoryName, STUFF
 ((SELECT     ',' + Product .ProductName
 FROM         tblProduct  Product
 WHERE     (CategoryID = Category.CategoryID) FOR XML PATH('')), 1, 1, '') AS Products
 FROM         tblCategory  Category

Result will be

CategoryName	Products
Electronics	Laptop, LCD TV,DVD Player

Visit xml path in sql