Loading

FOR XML PATH IN SQL

Pankaj Rana

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

Comments

Join the conversation! Your thoughts help the community grow.

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