id,  params                  ,  xml
1,   $pmn=67676$tns=67678787$=pro ,<ConsentDTO><OrderId>76676</OrderId><ReceiverInformation>              <ReceiverType>SMS</ReceiverType><CellPhoneNumber>78787</CellPhoneNumber></ReceiverInformation></ConsentDTO>

select SUBSTRING(params,
   CHARINDEX('$tnr=',params) + 5,
   CHARINDEX('$pro',params) - CHARINDEX('$tnr=',params)-5) from table1

select table1.xml.value('(/ConsentDTO/ReceiverInformation/CellPhoneNumber/text())[1]','varchar(50)') from table1)

SELECT (CASE WHEN params IS NULL THEN '' ELSE params END) as params FROM Table1

SELECT *
FROM table1
WHERE xmlColumn.exist('/Path/to/data') = 1;