How to achieve comma seperated entries in a table,expand in seperate rows by using xml.......

Hi,
 If you have data like this in your table:

AppId   Gender
1               x
2              y
3               x, y
4              x, y, z

and  you need to transform like this:
AppID          Gender
1                  x
2                  y
3                  x
3                  y
4                  x
4                  y
4                  z

Then you can achieve it without using loop or cursor, like this :

DECLARE @t TABLE (AppID INT, Gender VARCHAR(100))

INSERT INTO @t(AppID, Gender)

SELECT 1, 'x' UNION ALL

SELECT 2, 'y' UNION ALL

SELECT 3, 'x,y' UNION ALL

SELECT 4, 'x,y,z'

SELECT AppID,

i.value('.', 'VARCHAR(20)') AS Item

FROM

(

    SELECT

    AppID, Gender,

    CAST('<i>' + REPLACE(Gender, ',', '</i><i>') + '</i>' AS XML) AS GenderXml

    FROM @t

) a

CROSS APPLY GenderXml.nodes('//i') x(i)

Thanks

Puneet Kumar