DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='HARIA|123|MALE|STUDENT|HOUSEWIFE'
SET @xml = cast(('<X>'+replace(@str,'|' ,'</X><X>')+'</X>') as xml)
SELECT ShrededData as DesiredResult FROM(
SELECT
ROW_NUMBER() over(order by getdate()) rn
,N.value('.', 'varchar(10)') as ShrededData FROM @xml.nodes('X') as T(N))X
WHERE X.rn = 3 --- CHECKING WITH(1,2,3,4,5) 
