Try something like:

declare @x varchar(2000)

set @x='<Emp>

<Details>

<D N="Id" V="1235" />

<D N="Des" V="SW" />

<D N="name" V="AAA" />

<D N="name" V="BBB" />

<D N="name" V="CCC" />

<D N="name" V="DDD" />

</Details>

</Emp>'

;with XMLCTE as (select cast(@x as XML) xmlfield)

select xmlfield,r.value('(.)/@V','varchar(350)')

from xmlcte

cross apply xmlfield.nodes('/Emp/Details/D[@N="name"]') a(r)