Shashi, seems that you have MySQL 5.7 or even older version. This should work:
drop procedure if exists get_path;
delimiter $$
create procedure get_path(in document_node_id bigint(20), out path text)
begin
declare parent_name varchar(255);
declare temppath text;
declare tempparent bigint(20);
declare continue handler for not found
begin
set tempparent = null;
set parent_name = '';
end;
set max_sp_recursion_depth = 255;
select a.parent_node_id, p1.string_value from
alf_child_assoc a left join alf_node_properties p1 on a.parent_node_id=p1.node_id
where
p1.qname_id=(
select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id)
where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
)
and
a.child_node_id=document_node_id
into tempparent, parent_name;
if tempparent is null
then
set path = parent_name;
else
call get_path(tempparent, temppath);
set path = concat(temppath, '/', parent_name);
end if;
end$$
delimiter ;
drop function if exists get_path;
delimiter $$
create function get_path(child_node_id int) returns text deterministic
begin
declare res text;
call get_path(child_node_id, res);
return res;
end$$
delimiter ;Original idea is from here: https://stackoverflow.com/a/42734226/14914433
