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