SQL

How to use xml document as data type in the SQL server data base and select rows from Xml.


declare  @Records as xml

set @Records='     Validate="None" />'

 

SELECT

cast( Tbl.Col.value('@ID', 'nvarchar(50)')  as int) AS ID,

       NULLIF(Tbl.Col.value('@Name', 'NVARCHAR(20)'),'') AS Name,

       NULLIF(Tbl.Col.value('@Validate', 'NVARCHAR(50)') ,'') AS Validate

INTO #Records

FROM   @Records.nodes('//row') Tbl(Col)

 

 

select * from #Records

 

ID	Name	Validate
0	Test	None

 

How to find second highest salary from Employee table 

select EmpId,EmpName  from  (select EmpId,EmpName , ROW_NUMBER() over(order by salary desc) RowNum,Salary

 from dbo.tbl_Employee) as A where a. RowNum=2