I was so close, thanks to the QotD that made me remember the PARSENAME function.

My code

SELECT @HOLDER = ID

FROM #testEnvironment

WHERE PARSENAME( CAST(yourData AS NVARCHAR(MAX)),3) IS NULL

AND PARSENAME(CAST(yourData AS NVARCHAR(MAX)), 2) IS NOT NULL

Results using Cadavre's last test environment (without the XML). I deleted the "DBCC execution completed" comments.

Duration for CHARINDEX = 00:00:18:363

Duration for LEN = 00:00:09:347

Duration for PARSENAME = 00:00:06:870

Duration for COLLATE = 00:00:06:667