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
