declare @t table 
	(someStringField varchar(256))
	
insert @t 
values 
	('XXX_yyy_zzz_QQQ')
	,('123_456_789_0aa')
	,('abc_def_ghi_jkl')
	,('jhduthe_nsognfhdteigh_skweyur_ebgfvdn')

select PARSENAME(REPLACE(someStringField,'_','.'),3)
from @t

declare @yourTable table 
	(i int identity (1,1), someStringField varchar(256))
	
insert @yourTable 
values 
	('XXX_yyy_zzz_QQQ_...')
	,('123_456_789_0aa_..._..._......._')
	,('abc_def_ghi_jkl')
	,('jhduthe_nsognfhdteigh_skweyur_ebgfvdn')

SELECT
	i
	,targetNode
FROM
	(SELECT 
		yt.i
		,ROW_NUMBER() OVER (PARTITION BY yt.i ORDER BY N) as rowNum
		,SUBSTRING('_'+yt.someStringField+'_',N+1,CHARINDEX('_','_'+yt.someStringField+'_',N+1)-N-1) AS targetNode
	FROM 
		dbo.Tally t
			CROSS JOIN @yourTable yt
	WHERE 
		N < LEN('_'+yt.someStringField+'_')
		AND SUBSTRING('_'+yt.someStringField+'_',N,1) = '_') as myBrainHurts
WHERE
	rowNum = 3 ---- or which ever node you want to get to

SELECT i, targetNode=item
FROM @yourTable
CROSS APPLY dbo.DelimitedSplit8K(someStringField, '_')
WHERE itemnumber = 3