UFN_EVENTHIERARCHY_NODES

CREATE function dbo.UFN_EVENTHIERARCHY_NODES
(
    @HIERARCHYPATH hierarchyid,
    @SELECTEDEVENTID uniqueidentifier
)
returns @NODES table (
    CONTEXTID uniqueidentifier,
    CAPTION nvarchar(100),
    PAGEID uniqueidentifier,
    NODES xml,
    IMAGEKEY nvarchar(100)
)
as begin
    insert into
        @NODES
    select
        EVENT.ID [CONTEXTID],
        EVENT.NAME [CAPTION],
        '9988B807-97B2-434C-8BE1-BBEE6B944B2C' [PAGEID],
        (select * from dbo.UFN_EVENTHIERARCHY_NODES(EVENTHIERARCHY.HIERARCHYPATH, @SELECTEDEVENTID) for xml raw('NODE'), elements, type) [NODES],
        -- was trying out highlighting the context event: case when EVENT.ID = @SELECTEDEVENTID then 'RES:bullet_diamond' else '' end [IMAGEKEY]

        '' [IMAGEKEY]
    from
        dbo.EVENT
    left outer join
        dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
    where
        EVENTHIERARCHY.HIERARCHYPATH.GetAncestor(1) = @HIERARCHYPATH;

    return;
end