Friday 25 May 2012

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

CategoryName
Products
Electronics
Laptop, LCD TV,DVD Player

2 comments:

  1. nice articles pankaj rana.
    thanks

    ReplyDelete
    Replies
    1. nice articles pankaj rana.
      thanks

      Delete