You can make use of the ASCII function and convert it that way.
declare @counter as nvarchar
set @counter = 'A'
select ascii (@counter) -64 as Counter
The acsii value for uppercase A is 65, so simply knock 64 off the result. Bear in mind that lowercase values are different so you may want to force it to upper unless you're confident that the value in the xml is always uppercase.
In your case
INSERT INTO SECONDTABLE(COUNTER)
select ascii (t.COUNTER) -64 as Counter
FROM #temp T