SQL get specific element Count in XML

 Consider this XML

Suppose you want to get count of student ids. You can use SQL to get the count as follows.

declare @xml XML;

set @xml ='<Students>

    <Student>

        <Id>001</Id>

        <Fname>Leesa</Fname>

        <Sname>Humpry</Sname>

    </Student>

    <Student>

        <Id>002</Id>

        <Fname>Clara</FName>

        <Sname>Margrette</Sname>

    </Student>

  </Students>';

select @xml.value('count(/Students/Student/Id)', 'INT') AS 'Count'

Cheers

Samitha

sql server remove duplicates from query

You can use SQL row_number() function to remove duplicate rows from a query.

Create table Employees (fname varchar(100), lname varchar(100))

Insert into Employees Values 

               ('Tim', 'May')

             , ('Clara', 'Magrette') 

             , ('Clara', 'Magrette')

             , ('Jeff', 'Lawry') 

             , ('Brian', 'Adams')

             , ('Paul', 'Oliver')

As you can see there are duplicate records in the Employees table,

Select * from(

SELECT fname 

       ,lname 

       ,row_number() over (partition by fname , lname order by fname , lname ) as rownum 

FROM Employees 

) result

where rownum= 1 -- selects DISTINCT Employees only

Cheers,

Samitha