VARCHAR(MAX) acting weird when concatenating string

Question

I have a concatenating query:

DECLARE @path NVARCHAR(max) 
SELECT @path = ISNULL(@path + '/', '') + url_segment 
    FROM navigation_self_and_parents(2813) ORDER BY depth ASC
SELECT @path

navigation_self_and_parents(2813) returns

id    par_id  title        url_segment    sequence        depth
2813  2816    testing1234  testing1234    0               0   
2816  2809    U            /fixedurl      0               -1   
2809  NULL    E            E              0               -2   

My concatenating query returns

'testing1234'             when using `NVARCHAR(MAX)` and
'E//fixedurl/testing1234' when using `NVARCHAR(4000)`

My best guess is that using NVARCHAR(MAX) causes @path to be retyped every time it's set and thus losing the contents set prior to retyping or it's typed the first time it's set and then subsequent concatenating calls silently fail.

I would love to truly understand the root cause of this behavior though.

UPDATE

navigation_self_and_parents:

USE [SomeDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[navigation_self_and_parents]
(   
    @id int
)
RETURNS TABLE 
AS
RETURN 
(
    WITH navigation_self_and_parents (id, parent_id, title, url_segment, sequence_number, depth)
    AS
    (
        SELECT id, parent_id, title, url_segment, sequence_number, 0 FROM navigation_node WHERE id=@id 
        UNION ALL

        SELECT n.id, n.parent_id, n.title, n.url_segment, n.sequence_number, depth - 1 From navigation_node as n
        INNER JOIN navigation_self_and_parents as rn
        ON n.id = rn.parent_id
    )
    SELECT * FROM navigation_self_and_parents
)

navigation_node DDL:

CREATE TABLE [dbo].[navigation_node](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](128) NULL,
    [url_segment] [nvarchar](max) NULL,
    [hidden] [bit] NOT NULL,
    [page_id] [int] NULL,
    [parent_id] [int] NULL,
    [sequence_number] [int] NOT NULL,
    [createdOn] [datetime] NOT NULL,
    [updatedOn] [datetime] NULL,
    [navigation_type_id] [int] NULL,
    ...snap

Answer 1

This approach to string concatenation does usually work but it isn't guaranteed.

The official line in the KB article for a similar issue is that "The correct behavior for an aggregate concatenation query is undefined."

There must be some subtle difference between the plans. You can either tweak the query to remove the difference and get the required execution plan or you could/should of course just use XML PATH as you are on SQL Server 2005 and this is documented to work.