Oracle SYS_CONNECT_BY_PATH equivalent query into SQL Server

CREATE TABLE #MY_TABLE
(
     ID         INT
    ,ID_FATHER  INT
    ,COL_X      INT
)

CREATE TABLE #MY_TABLE_BIS
(
     MY_ID  INT
    ,X      VARCHAR(50)
)

CREATE TABLE #OTHER_TABLE
(
     MY_ID  INT
    ,[ROOT] VARCHAR(50)
)

CREATE TABLE #BIG_TABLE
(
     AN_ID  INT
)
go

DECLARE @MAXDEPTH INT = 10

;WITH
    cte_prepare
    AS
    (
        SELECT
             ID
            ,ID_FATHER
            ,TB.X
            ,OT.[ROOT]
        FROM #MY_TABLE T
        LEFT JOIN #MY_TABLE_BIS TB
                    ON TB.MY_ID = COL_X
        LEFT JOIN #OTHER_TABLE OT
                    ON OT.MY_ID = COL_X
    ),
    cte_connect (LVL, ID, X_ALIAS, X_ALIAS_FATHER, X_ALIAS_LEAF)
    AS
    (
        SELECT 1
            ,T.ID
            ,CAST(CONCAT(T.[ROOT], '.C_', T.X) AS VARCHAR(MAX))
            ,CAST(CONCAT(T.[ROOT], '.') AS VARCHAR(MAX))
            ,CAST(T.X AS VARCHAR(30))
        FROM cte_prepare T
        WHERE T.ID_FATHER IN
            (
                SELECT AN_ID AS ID_FATHER
                FROM #BIG_TABLE
            )

        UNION ALL

        SELECT F.LVL + 1
             ,S.ID
             ,CAST(CONCAT(F.X_ALIAS, '.C_' + S.X) AS VARCHAR(MAX))
             ,CAST(F.X_ALIAS AS VARCHAR(MAX))
             ,CAST(S.X AS VARCHAR(30))
        FROM cte_prepare S
            INNER JOIN cte_connect F
                        ON S.ID_FATHER = F.ID
        WHERE F.LVL < @MAXDEPTH
    )
SELECT DISTINCT
     CT.X_ALIAS
    ,CT.X_ALIAS_FATHER
    ,CT.X_ALIAS_LEAF
    ,CT.LVL
FROM cte_connect CT
-- Uncomment this and set depth when it's greater than 100.
--OPTION (MAXRECURSION 0) -- value between 0 and 32,767 (default is 100), 0 = unlimited

see also
https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15