Sol 3

I sometimes forget how to do an XML output formed in a tree when dealing with a single table parent/child relationship.  There is a great explanation on SQL Server Central on *how* to do this.  I am mainly capturing the link and the SQL I just generated to do this.  This query will drill down 12 levels at the most.  Just alter the case statement (pivot) to go deeper.

ALTER PROC getOrgUnitTreeAsXml
AS
BEGIN
    ;WITH OrgUnit1
    AS
    (
        SELECT
            0 AS [Level],
            [OrgUnitId],
            [orgUnitParentID],
            [orgUnit],
            CAST( [orgUnitID] AS VARBINARY(MAX)) AS Sort
        FROM [orgUnit]
        WHERE [orgUnitParentID] IS NULL
        UNION ALL
        SELECT
            [Level] + 1,
            p.[OrgUnitId],
            p.[orgUnitParentID],
            p.[orgUnit],
            CAST( SORT + CAST(p.[orgUnitID] AS BINARY(4)) AS VARBINARY(MAX))
        FROM [orgUnit] p
        INNER JOIN OrgUnit1 c ON p.[orgUnitParentID] = c.[OrgUnitId]
    )
    ,  OrgUnit2 AS
    (
        SELECT
            [Level] + 1 AS Tag,
            [OrgUnitId],
            [orgUnitParentID],
            [orgUnit],
            sort
        FROM OrgUnit1
    )
    , OrgUnit3 AS
    (
        SELECT
            *,
            (SELECT Tag FROM OrgUnit2 r2 WHERE r2.[OrgUnitId] = r1.[orgUnitParentID]) AS ParentTag
        FROM OrgUnit2 r1
    )
    SELECT Tag, ParentTag as Parent,
    CASE WHEN tag = 1 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!1!id',
        CASE WHEN tag = 1 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!1!name',
    CASE WHEN tag = 2 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!2!id',
        CASE WHEN tag = 2 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!2!name',
    CASE WHEN tag = 3 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!3!id',
        CASE WHEN tag = 3 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!3!name',
    CASE WHEN tag = 4 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!4!id',
        CASE WHEN tag = 4 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!4!name',
    CASE WHEN tag = 5 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!5!id',
        CASE WHEN tag = 5 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!5!name',
    CASE WHEN tag = 6 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!6!id',
        CASE WHEN tag = 6 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!6!name',
    CASE WHEN tag = 7 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!7!id',
        CASE WHEN tag = 7 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!7!name',
    CASE WHEN tag = 8 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!8!id',
        CASE WHEN tag = 8 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!8!name',
    CASE WHEN tag = 9 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!9!id',
        CASE WHEN tag = 9 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!9!name',
    CASE WHEN tag = 10 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!10!id',
        CASE WHEN tag = 10 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!10!name',
    CASE WHEN tag = 11 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!11!id',
        CASE WHEN tag = 11 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!11!name',
    CASE WHEN tag = 12 THEN [OrgUnitId] ELSE NULL END AS 'OrgUnit!12!id',
        CASE WHEN tag = 12 THEN [orgUnit] ELSE NULL END AS 'OrgUnit!12!name'
    FROM OrgUnit3
    ORDER BY sort
    FOR XML EXPLICIT
END
GO