Select children from hierarchy (/a/b/c/d/)
You need to 'walk the tree'.  This can be done using a recursive CTE, something like this:

USE tempdb
GO

CREATE TABLE dbo.Unit (
    id INT IDENTITY PRIMARY KEY,
    unitName        VARCHAR(20),
    hierarchyPath   hierarchyId UNIQUE,
    typeId          INT
    )
GO

CREATE TABLE dbo.UnitType (
    id      INT PRIMARY KEY,
    name    VARCHAR(20)
    )
GO

INSERT INTO dbo.Unit ( hierarchyPath, unitName, typeId )
VALUES 
    ( '/42/', 'Team1', 1 ),
    ( '/42/1/', 'John', 2 ),
    ( '/42/2/', 'Joe', 2 ),
    ( '/42/3/', 'Johana', 2 ),

    ( '/43/', 'Team2', 1 ),
    ( '/43/1/', 'Bob', 2 ),
    ( '/43/2/', 'Bobek', 2 ),
    ( '/43/3/', 'wBobaFett', 2 )

INSERT INTO dbo.UnitType ( Id, name )
VALUES ( 1, 'Team' ), ( 2, 'Name' )
GO

--SELECT *, hierarchyPath.ToString() hierarchyPath_string
--FROM dbo.Unit

;WITH cte AS
(
SELECT 1 xlevel, Id, hierarchyPath, unitName AS teamName, CAST( '' AS VARCHAR(20) ) unitName
FROM dbo.Unit
WHERE typeId = ( SELECT Id FROM dbo.UnitType WHERE name = 'Team' )

UNION ALL

SELECT xlevel + 1, u.Id, u.hierarchyPath, c.teamName, u.unitName
FROM cte c
    INNER JOIN dbo.Unit u ON u.hierarchyPath.GetAncestor (1) = c.hierarchyPath

)
SELECT teamName, unitName
FROM cte
WHERE xlevel &gt; 1
ORDER BY teamName, unitName
GO

DROP TABLE dbo.Unit
DROP TABLE dbo.UnitType
GO


HTH