DECLARE @categories TABLE
(
	stocknode_id INT,
	short_desc VARCHAR(255),
	parentnode_id INT
)

INSERT INTO @categories VALUES (1, 'A - Top Level', 0);
INSERT INTO @categories VALUES (2, 'A - Second Level', 1);
INSERT INTO @categories VALUES (3, 'A - Third Level', 2);
INSERT INTO @categories VALUES (4, 'A - Fourth Level', 3);

INSERT INTO @categories VALUES (5, 'B - Top Level', 0);
INSERT INTO @categories VALUES (6, 'B - Second Level', 5);
INSERT INTO @categories VALUES (7, 'B - Third Level', 6);
INSERT INTO @categories VALUES (8, 'B - Fourth Level', 7);

INSERT INTO @categories VALUES (9, 'C - Top Level', 0);
INSERT INTO @categories VALUES (10, 'C - Second Level', 9);
INSERT INTO @categories VALUES (11, 'C - Third Level', 10);
INSERT INTO @categories VALUES (12, 'C - Fourth Level', 11);

WITH recursion(stocknode_id, short_desc, parentnode_id, level) AS
(
	SELECT
		stocknode_id,
		short_desc,
		parentnode_id,
		0 AS level
	
	FROM @categories AS swn
	
	WHERE swn.stocknode_id = 8
	
	UNION ALL
	
	SELECT
		swn.stocknode_id,
		swn.short_desc,
		swn.parentnode_id,
		h.level + 1
	
	FROM @categories AS swn
	
	INNER JOIN recursion AS h ON h.parentnode_id = swn.stocknode_id
)

SELECT
	TOP 1 STUFF
	(
		(
			SELECT ' / ' + swn_stuff.short_desc
			FROM @categories AS swn_stuff
			WHERE swn_stuff.stocknode_id IN (SELECT stocknode_id FROM recursion)
			FOR XML PATH(''), TYPE
        ).value('.','varchar(max)'),
        1, 2, ''
	 ) AS category_tree

FROM @categories AS swn_a