openxml

forexample

 

declare @NewWork xml='<NewWork>
  <Work Mno="100009">
    <Sno0 Sno="10010001"></Sno0>
    <Sno0 Sno="10010002"></Sno0>
  </Work>
</NewWork>'

declare @idoc int

exec sp_xml_preparedocument @idoc output,@NewWork
 select *
 from openxml(@idoc,'/NewWork/Work/Sno0',2)
 with(Mno int '../@Mno',
      Sno int '@Sno')
 exec sp_xml_removedocument @idoc