Storing URIs in SQL Server 2008 using the HIERARCHYID datatype and LINQ-To-SQL

CREATE TABLE dbo.URI
(
	Id uniqueidentifier NOT NULL,
	Label nvarchar(256) NULL,
	UriHID hierarchyid NOT NULL,           -- the magical new datatype
	CONSTRAINT [PK_URI] PRIMARY KEY
)

  MyLINQDataContext db = new MyLINQDataContext(); // a previously defined context via .dbml file
  System.Guid? uri_id=null;			  // variable to capture the URI.Id of the last node inserted
  db.InsertURI("/products/vcm/index.html", ref uri_id);	// call to stored proc
  Console.Out.Writeline( uri_id );		 // doing something useful with the return result

CREATE Procedure InsertURI
	@uri nvarchar(4000),
	@uri_id uniqueidentifier OUTPUT
AS
	declare @root_uri_id uniqueidentifier
	declare @path_remainder nvarchar(4000)
	declare @pos_slash int
	declare @parent_uri_hid HIERARCHYID
 
	SET @parent_uri_hid = HIERARCHYID::GetRoot()
 
	-- ensure the root exists
	SELECT @root_uri_id = u.id FROM URI u WHERE u.label IS NULL
	IF @root_uri_id IS NULL
		INSERT INTO URI VALUES (NEWID(), NULL, @parent_uri_hid)
 
	SET @path_remainder = @uri
	SET @pos_slash = charindex( '/', @path_remainder )
	while (len(@path_remainder) > 0)
	begin
		declare @next_slash int
		declare @uri_label nvarchar(256)
		declare @current_uri_hid HIERARCHYID
		SET @next_slash = charindex( '/', @path_remainder, @pos_slash+1 )
 
		-- determine the next label in the sequence of depth-first node names
		IF @next_slash > 0
		begin
			SET @uri_label = SUBSTRING( @path_remainder, @pos_slash+1, @next_slash - @pos_slash - 1 )
			SET @path_remainder = substring( @path_remainder, @next_slash, len(@path_remainder) )
		end
		else
		begin
			SET @uri_label = SUBSTRING( @path_remainder, @pos_slash+1, len(@path_remainder) )
			SET @path_remainder = ''
		end
		SET @pos_slash = charindex( '/', @path_remainder )
		-- determine if current @uri_label exists as child of @parent_uri_hid
		SET @current_uri_hid = NULL
		SELECT @current_uri_hid = u.UriHID FROM URI u WHERE u.UriHID.GetAncestor(1) = @parent_uri_hid AND u.Label=@uri_label
		IF @current_uri_hid IS NULL
		begin
			-- the label doesn't exist as a child, hence create it
			-- first determine the new hierarchyid - new node will be last in row of siblings
			declare @last_child_uri_hid HIERARCHYID
			SELECT @last_child_uri_hid = MAX(UriHID) FROM URI u WHERE u.UriHID.GetAncestor(1) = @parent_uri_hid
			SET @current_uri_hid = @parent_uri_hid.GetDescendant(@last_child_uri_hid, NULL)
 
			SET @uri_id = NEWID()
			INSERT INTO uri VALUES (@uri_id, @uri_label, @current_uri_hid )
		end
		SET @parent_uri_hid = @current_uri_hid
	end