Querying attribute and node values of XML data in SQL

DECLARE @xmlData xml

set @xmlData = '<University Name="VTU">
 <COLLEGE Name="SJCE">
 <DESCRIPTION>SJCE Mysore</DESCRIPTION>
 <Address field="State">Karnataka</Address> 
 <Address field="Country">India</Address> 
 </COLLEGE>
<COLLEGE Name="NIE">
 <DESCRIPTION>NIE Mysore</DESCRIPTION>
 <Address field="State">Karnataka</Address> 
 <Address field="Country">India</Address> 
 </COLLEGE>
 </University>'

SELECT 
 U.C.value('(@Name)[1]', 'varchar(max)') as CollegeName,
 U.C.value('(DESCRIPTION)[1]', 'varchar(max)') as CollegeDescription ,
 U.C.value('(Address[@field="State"])[1]', 'varchar(max)') as StateName,
 U.C.value('(Address[@field="Country"])[1]', 'varchar(max)') as CountryName
FROM @xmlData.nodes('/University/COLLEGE') as U(C)

The above SELECT SQL Query will display below result:

XmlQueryResult

Keep Single Record from Dublicate Records

Example:

DECLARE @StudentRollNo varchar(6)
DECLARE @StudentName varchar(8)
DECLARE @DublicateCount int

DECLARE DEL_CURSOR CURSOR FOR
SELECT StudentRollNo, StudentName, count(StudentRollNo) FROM TblStudent group by StudentRollNo, StudentName having count(StudentRollNo) > 1

OPEN DEL_CURSOR

FETCH NEXT FROM DEL_CURSOR INTO @StudentRollNo, @StudentName, @DublicateCount

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE TOP(@DublicateCount -1) FROM TblStudent WHERE 
    StudentRollNo = @StudentRollNo and 
    StudentName = @StudentName     

FETCH NEXT FROM DEL_CURSOR INTO  @StudentRollNo, @StudentName, @DublicateCount
END

CLOSE DEL_CURSOR
DEALLOCATE DEL_CURSOR