FOR XML PATH IN SQL

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


Next Recommended Reading Convert Data Table to XML in SQL Server