Sreenivasa Reddy Thotli

SELECT ( SELECT 'White' AS Color1, 'Blue' AS Color2, 'Black' AS Color3, 'Light' AS 'Color4/@Special' , 'Green' AS Color4, 'Red' AS Color5 FOR XML PATH('Colors'), TYPE ), ( SELECT 'Apple' AS Fruits1, 'Pineapple' AS Fruits2, 'Grapes' AS Fruits3, 'Melon' AS Fruits4 FOR XML PATH('Fruits'), TYPE ) FOR XML PATH(''), ROOT('SampleXML') /*Out Put*/ <SampleXML>   <Colors>     <Color1>White</Color1>     <Color2>Blue</Color2>     <Color3>Black</Color3>     <Color4 Special="Light">Green</Color4>     <Color5>Red</Color5>   </Colors>   <Fruits>     <Fruits1>Apple</Fruits1>     <Fruits2>Pineapple</Fruits2>     <Fruits3>Grapes</Fruits3>     <Fruits4>Melon</Fruits4>   </Fruits> </SampleXML>

/* Create Table with 7 entries - 3 are duplicate entries */ CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT) INSERT INTO DuplicateRcordTable SELECT 1, 1 UNION ALL SELECT 1, 1 --duplicate UNION ALL SELECT 1, 1 --duplicate UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 2 --duplicate UNION ALL SELECT 1, 3 UNION ALL SELECT 1, 4 SELECT * FROM DuplicateRcordTable /* Delete Duplicate records */ WITH CTE (COl1,Col2, DuplicateCount) AS ( SELECT COl1,Col2, ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount FROM DuplicateRcordTable ) DELETE FROM CTE WHERE DuplicateCount > 1