WITH 
    FUNCTION tmp_func RETURN XMLTYPE IS 
    BEGIN 
        RETURN XMLTYPE('<ROW><VALUE>TEST</VALUE></ROW>'); 
    END; 
temp_1 AS ( 
    SELECT t.result 
    FROM XMLTABLE('/' 
        PASSING tmp_func() 
        COLUMNS result VARCHAR2(4) PATH 'VALUE' 
    ) t 
) 
, temp_2 AS ( 
    SELECT t.result 
    FROM XMLTABLE('/' 
        PASSING tmp_func() 
        COLUMNS result VARCHAR2(4) PATH 'VALUE' 
    ) t 
) 
SELECT t1.result FROM temp_1 t1 
UNION ALL 
SELECT t2.result FROM temp_2 t2 

WITH 
    FUNCTION tmp_func RETURN XMLTYPE IS 
    BEGIN 
        RETURN XMLTYPE('<ROW><VALUE>TEST</VALUE></ROW>'); 
    END; 
temp_1 AS ( 
    SELECT t.result 
    FROM XMLTABLE('/' 
        PASSING tmp_func() 
        COLUMNS result VARCHAR2(4) PATH 'VALUE' 
    ) t 
) 
SELECT t1.result FROM temp_1 t1 
UNION ALL 
SELECT t2.result FROM temp_1 t2 

WITH temp_1 AS ( 
    SELECT t.result 
    FROM XMLTABLE('/' 
        PASSING XMLTYPE('<ROW><VALUE>TEST</VALUE></ROW>') 
        COLUMNS result VARCHAR2(4) PATH 'VALUE' 
    ) t 
) 
SELECT t1.result FROM temp_1 t1 
UNION ALL 
SELECT t2.result FROM temp_1 t2