There is a well-known issue with the xml datatype and methods and parent axis.

Try this instead:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

GO

SELECT CAST( ConsolidatedPlayersCombinedText AS XML ) ConsolidatedPlayersCombinedText

INTO #tmp

FROM tour_feeds WHERE id = 1

SELECT

p.c.value('@id', 'INT') player_id,

r.c.value('@n', 'INT') round_num,

h.c.value('@cNum', 'INT') hole_number,

h.c.value('@sc', 'INT') shots,

s.c.value('@time', 'VARCHAR(6)') last_shot_time

FROM #tmp t

CROSS APPLY t.ConsolidatedPlayersCombinedText.nodes('ConsolidatedPlayers/p') p(c)

CROSS APPLY p.c.nodes('rnd') r(c)

CROSS APPLY r.c.nodes('holes/h') h(c)

CROSS APPLY h.c.nodes('shot[last()]') s(c)