Truncate path into descending subpaths

I have a path that looks like this

Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5

The length is variable, and it is possible that it could be NULL as well.

How could I create 5 columns such that

Column 1 shows the root, column 2 shows the root and the next node, etc

So column 3 would be Subdomain1>Subdomain2>Subdomain3 in this case

If the path was

Subdomain1>Subdomain2

Then the column 3, 4 and 4 would be null

and of the path was

Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5>Subdomain6

then column 5 would be Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5

We are running SQL Server 2016

Please try the following solution based on tokenization and leveraging SQL Server XML and XQuery functionality.

Notable points:

• 1st CROSS APPLY is converting path to XML.
• 2nd CROSS APPLY is counting number of tokens in the path.
• Calculated col1 - col5 columns are using XQuery .query() method to get path's subset that is matching column#.
• Logic based on IIF() function gets us NULL value for appropriate columns.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, _path VARCHAR(256));
INSERT INTO @tbl (_path) VALUES
('Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5'),
('Subdomain1>Subdomain2');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '>';

SELECT *
    , col1 = IIF(cnt < 1, NULL, REPLACE(c.query('data(/root/r[position() le 1])')
        .value('.', 'VARCHAR(256)'),SPACE(1),@separator))
    , col2 = IIF(cnt < 2, NULL, REPLACE(c.query('data(/root/r[position() le 2])')
        .value('.', 'VARCHAR(256)'),SPACE(1),@separator))
    , col3 = IIF(cnt < 3, NULL, REPLACE(c.query('data(/root/r[position() le 3])')
        .value('.', 'VARCHAR(256)'),SPACE(1),@separator))
    , col4 = IIF(cnt < 4, NULL, REPLACE(c.query('data(/root/r[position() le 4])') 
        .value('.', 'VARCHAR(256)'),SPACE(1),@separator))
    , col5 = IIF(cnt < 5, NULL, REPLACE(c.query('data(/root/r[position() le 5])') 
        .value('.', 'VARCHAR(256)'),SPACE(1),@separator))
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(_path, @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c)
CROSS APPLY (SELECT c.value('count(/root/r)', 'INT')) AS t2(cnt);

Output