Retreiving field value in XML using cross apply

1)  First we will cast the field data in a table to type xml from type text

2) you need to use with operator if namespace is defined ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Psv.Domain.Models.PexaNoa')

3) consider ContentXML as 

     '<Name>

         <First>Jacob</First>

         <Last>Sebastian</Last>

     </Name>'

Begin

 SELECT ContentXML INTO #tab12
FROM 
 (Select  
 Cast(data as xml) AS ContentXML
 from ABC
 ) a



 ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org')

 SELECT t.value('(./First)[1]','NVARCHAR(MAX)') as FirstName
  FROM #tab12
Cross Apply ContentXML.nodes('/Name') x(t) 

 Drop table #tab12
End