Jeff,

This was timely as we are working on a tree just like this. I'm curious what you think about replacing the SortPath with a hierarchyid data type?

Glenn

CREATE FUNCTION [dbo].[Split](@data VARCHAR(MAX), @delimiter VARCHAR(20))

RETURNS @t TABLE (Element VARCHAR(MAX))

AS

BEGIN

DECLARE @textXML XML

SET @textXML = CAST('<d>' + REPLACE(REPLACE(REPLACE(@data,'&','~~amper~~'),'<','~~lt~~'), @delimiter, '</d><d>') + '</d>' AS...

You should use ROW_NUMBER() instead of rank. Rank will assign the same number to the first to items in your data set. They are both 1 so...