Create hierarchy of multiple levels where each node has a random number of children

(O.P.'s note: preferred solution is the 4th / final code block)

XML seems to me to be the obvious choice of data structure to use here.

with N as
(
  select T.N
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
              (12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)

select top(5 + abs(checksum(newid())) % 15)
  N1.N as '@Value',
  (
  select top(1 + abs(checksum(newid())) % 10)
    N2.N as '@Value',
    (
    select top(1 + abs(checksum(newid())) % 5)
      N3.N as '@Value'
    from N as N3
    where N2.N > 0
    for xml path('Level3'), type
    )
  from N as N2
  where N1.N > 0
  for xml path('Level2'), type
  )
from N as N1
for xml path('Level1'), root('Root');

The trick to make SQL Server use different values for top() for each node is to make the sub-queries correlated. N1.N > 0 and N2.N > 0.

Flatteing the XML:

declare @X xml;

with N as
(
  select T.N
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
              (12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)
select @X  = (
             select top(5 + abs(checksum(newid())) % 15)
               N1.N as '@Value',
               (
               select top(1 + abs(checksum(newid())) % 10)
                 N2.N as '@Value',
                 (
                 select top(1 + abs(checksum(newid())) % 5)
                   N3.N as '@Value'
                 from N as N3
                 where N2.N > 0
                 for xml path('Level3'), type
                 )
               from N as N2
               where N1.N > 0
               for xml path('Level2'), type
               )
             from N as N1
             for xml path('Level1')
             );


select L1.X.value('@Value', 'varchar(10)')+'\'+
       L2.X.value('@Value', 'varchar(10)')+'\'+
       L3.X.value('@Value', 'varchar(10)')
from @X.nodes('/Level1') as L1(X)
  cross apply L1.X.nodes('Level2') as L2(X)
  cross apply L2.X.nodes('Level3') as L3(X);

And a version totally void of XML.

with N as
(
  select T.N
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
              (12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)
select cast(N1.N as varchar(10))+'\'+
       cast(N2.N as varchar(10))+'\'+
       cast(N3.N as varchar(10))
from (
     select top(5 + abs(checksum(newid())) % 15)
       N.N
     from N
     ) as N1
  cross apply
     (
     select top(1 + abs(checksum(newid())) % 10)
       N.N
     from N
     where N1.N > 0
     ) as N2
  cross apply
     (
     select top(1 + abs(checksum(newid())) % 5)
       N.N
     from N
     where N2.N > 0
     ) as N3;

Correlation N1.N > 0 and N2.N > 0 is still important.

A version using a table with 20 names to be used instead of just integers.

declare @Elements table
(
  Name nvarchar(50) not null
);

insert into @Elements(Name)
select top(20) C.name 
from sys.columns as C
group by C.name;

select N1.Name + N'\' + N2.Name + N'\' + N3.Name
from (
     select top(5 + abs(checksum(newid())) % 15)
       E.Name
     from @Elements as E
     ) as N1
  cross apply
     (
     select top(1 + abs(checksum(newid())) % 10)
       E.Name
     from @Elements as E
     where N1.Name > ''
     ) as N2
  cross apply
     (
     select top(1 + abs(checksum(newid())) % 5)
       E.Name
     from @Elements as E
     where N2.Name > ''
     ) as N3;

That was interesting.

My aim was to generate given number of levels with random number of child rows per each level in a properly linked hierarchical structure. Once this structure is ready it is easy to add extra info into it like file and folder names.

So, I wanted to generate a classic table for storing a tree:

ID int NOT NULL
ParentID int NULL
Lvl int NOT NULL

Since we are dealing with recursion, recursive CTE seems a natural choice.

I will need a table of numbers. Numbers in the table should start from 1. There should be at least 20 numbers in the table: MAX(LvlMax).

CREATE TABLE [dbo].[Numbers](
    [Number] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED 
(
    [Number] ASC
));

INSERT INTO Numbers(Number)
SELECT TOP(1000)
    ROW_NUMBER() OVER(ORDER BY S.object_id)  AS Number
FROM
    sys.all_objects AS S
ORDER BY Number;

Parameters for data generation should be stored in a table:

DECLARE @Intervals TABLE (Lvl int, LvlMin int, LvlMax int);
INSERT INTO @Intervals (Lvl, LvlMin, LvlMax) VALUES
(1, 5, 20),
(2, 1, 10),
(3, 1, 5);

Note, that the query is pretty flexible and all parameters are separated into one place. You can add more levels if needed, just add an extra row of parameters.

To make such dynamic generation possible I had to remember the random number of rows for the next level, so I have an extra column ChildRowCount.

Generating unique IDs is also somewhat tricky. I hard-coded the limit of 100 child rows per 1 parent row to guarantee that IDs don't repeat. This is what that POWER(100, CTE.Lvl) is about. As a result there are large gaps in IDs. That number could be a MAX(LvlMax), but I put constant 100 in the query for simplicity. The number of levels is not hard-coded, but is determined by @Intervals.

This formula

CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5

generates a random floating point number in the range [0..1), which is then scaled to the required interval.

The query logic is simple. It is recursive. First step generates a set of rows of the first level. Number of rows is determined by random number in TOP. Also, for each row there is a separate random number of child rows stored in ChildRowCount.

Recursive part uses CROSS APPLY to generate given number of child rows per each parent row. I had to use WHERE Numbers.Number <= CTE.ChildRowCount instead of TOP(CTE.ChildRowCount), because TOP is not allowed in recursive part of CTE. Didn't know about this limitation of SQL Server before.

WHERE CTE.ChildRowCount IS NOT NULL stops the recursion.

SQL Fiddle

WITH
CTE
AS
(
    SELECT 
        TOP(CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 1)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            AS int))
        Numbers.Number AS ID
        ,NULL AS ParentID
        ,1 AS Lvl
        ,CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 2)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            AS int) AS ChildRowCount
    FROM Numbers
    ORDER BY Numbers.Number

    UNION ALL

    SELECT
        CA.Number + CTE.ID * POWER(100, CTE.Lvl) AS ID
        ,CTE.ID AS ParentID
        ,CTE.Lvl + 1 AS Lvl
        ,CA.ChildRowCount
    FROM
        CTE
        CROSS APPLY
        (
            SELECT
                Numbers.Number
                ,CAST(
                    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
                    (
                    1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                      - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    )
                    + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    AS int) AS ChildRowCount
            FROM Numbers
            WHERE Numbers.Number <= CTE.ChildRowCount
        ) AS CA
    WHERE
        CTE.ChildRowCount IS NOT NULL
)
SELECT *
FROM CTE
ORDER BY Lvl, ParentID, ID;

Result (there can be up to 20 + 20*10 + 200*5 = 1220 rows if you are lucky)

+---------+----------+-----+-------------------+
|   ID    | ParentID | Lvl | ChildRowCount     |
+---------+----------+-----+-------------------+
|       1 | NULL     |   1 | 3                 |
|       2 | NULL     |   1 | 1                 |
|       3 | NULL     |   1 | 6                 |
|       4 | NULL     |   1 | 5                 |
|       5 | NULL     |   1 | 3                 |
|       6 | NULL     |   1 | 7                 |
|       7 | NULL     |   1 | 1                 |
|       8 | NULL     |   1 | 6                 |
|     101 | 1        |   2 | 3                 |
|     102 | 1        |   2 | 5                 |
|     103 | 1        |   2 | 1                 |
|     201 | 2        |   2 | 5                 |
|     301 | 3        |   2 | 4                 |
|     302 | 3        |   2 | 5                 |
|     303 | 3        |   2 | 1                 |
|     304 | 3        |   2 | 2                 |
|     305 | 3        |   2 | 4                 |
|     306 | 3        |   2 | 3                 |
|     401 | 4        |   2 | 3                 |
|     402 | 4        |   2 | 1                 |
|     403 | 4        |   2 | 2                 |
|     404 | 4        |   2 | 2                 |
|     405 | 4        |   2 | 4                 |
|     501 | 5        |   2 | 1                 |
|     502 | 5        |   2 | 3                 |
|     503 | 5        |   2 | 5                 |
|     601 | 6        |   2 | 2                 |
|     602 | 6        |   2 | 5                 |
|     603 | 6        |   2 | 3                 |
|     604 | 6        |   2 | 3                 |
|     605 | 6        |   2 | 4                 |
|     606 | 6        |   2 | 5                 |
|     607 | 6        |   2 | 4                 |
|     701 | 7        |   2 | 2                 |
|     801 | 8        |   2 | 2                 |
|     802 | 8        |   2 | 3                 |
|     803 | 8        |   2 | 3                 |
|     804 | 8        |   2 | 3                 |
|     805 | 8        |   2 | 5                 |
|     806 | 8        |   2 | 2                 |
| 1010001 | 101      |   3 | NULL              |
| 1010002 | 101      |   3 | NULL              |
| 1010003 | 101      |   3 | NULL              |
| 1020001 | 102      |   3 | NULL              |
| 1020002 | 102      |   3 | NULL              |
| 1020003 | 102      |   3 | NULL              |
| 1020004 | 102      |   3 | NULL              |
| 1020005 | 102      |   3 | NULL              |
| 1030001 | 103      |   3 | NULL              |
| 2010001 | 201      |   3 | NULL              |
| 2010002 | 201      |   3 | NULL              |
| 2010003 | 201      |   3 | NULL              |
| 2010004 | 201      |   3 | NULL              |
| 2010005 | 201      |   3 | NULL              |
| 3010001 | 301      |   3 | NULL              |
| 3010002 | 301      |   3 | NULL              |
| 3010003 | 301      |   3 | NULL              |
| 3010004 | 301      |   3 | NULL              |
| 3020001 | 302      |   3 | NULL              |
| 3020002 | 302      |   3 | NULL              |
| 3020003 | 302      |   3 | NULL              |
| 3020004 | 302      |   3 | NULL              |
| 3020005 | 302      |   3 | NULL              |
| 3030001 | 303      |   3 | NULL              |
| 3040001 | 304      |   3 | NULL              |
| 3040002 | 304      |   3 | NULL              |
| 3050001 | 305      |   3 | NULL              |
| 3050002 | 305      |   3 | NULL              |
| 3050003 | 305      |   3 | NULL              |
| 3050004 | 305      |   3 | NULL              |
| 3060001 | 306      |   3 | NULL              |
| 3060002 | 306      |   3 | NULL              |
| 3060003 | 306      |   3 | NULL              |
| 4010001 | 401      |   3 | NULL              |
| 4010002 | 401      |   3 | NULL              |
| 4010003 | 401      |   3 | NULL              |
| 4020001 | 402      |   3 | NULL              |
| 4030001 | 403      |   3 | NULL              |
| 4030002 | 403      |   3 | NULL              |
| 4040001 | 404      |   3 | NULL              |
| 4040002 | 404      |   3 | NULL              |
| 4050001 | 405      |   3 | NULL              |
| 4050002 | 405      |   3 | NULL              |
| 4050003 | 405      |   3 | NULL              |
| 4050004 | 405      |   3 | NULL              |
| 5010001 | 501      |   3 | NULL              |
| 5020001 | 502      |   3 | NULL              |
| 5020002 | 502      |   3 | NULL              |
| 5020003 | 502      |   3 | NULL              |
| 5030001 | 503      |   3 | NULL              |
| 5030002 | 503      |   3 | NULL              |
| 5030003 | 503      |   3 | NULL              |
| 5030004 | 503      |   3 | NULL              |
| 5030005 | 503      |   3 | NULL              |
| 6010001 | 601      |   3 | NULL              |
| 6010002 | 601      |   3 | NULL              |
| 6020001 | 602      |   3 | NULL              |
| 6020002 | 602      |   3 | NULL              |
| 6020003 | 602      |   3 | NULL              |
| 6020004 | 602      |   3 | NULL              |
| 6020005 | 602      |   3 | NULL              |
| 6030001 | 603      |   3 | NULL              |
| 6030002 | 603      |   3 | NULL              |
| 6030003 | 603      |   3 | NULL              |
| 6040001 | 604      |   3 | NULL              |
| 6040002 | 604      |   3 | NULL              |
| 6040003 | 604      |   3 | NULL              |
| 6050001 | 605      |   3 | NULL              |
| 6050002 | 605      |   3 | NULL              |
| 6050003 | 605      |   3 | NULL              |
| 6050004 | 605      |   3 | NULL              |
| 6060001 | 606      |   3 | NULL              |
| 6060002 | 606      |   3 | NULL              |
| 6060003 | 606      |   3 | NULL              |
| 6060004 | 606      |   3 | NULL              |
| 6060005 | 606      |   3 | NULL              |
| 6070001 | 607      |   3 | NULL              |
| 6070002 | 607      |   3 | NULL              |
| 6070003 | 607      |   3 | NULL              |
| 6070004 | 607      |   3 | NULL              |
| 7010001 | 701      |   3 | NULL              |
| 7010002 | 701      |   3 | NULL              |
| 8010001 | 801      |   3 | NULL              |
| 8010002 | 801      |   3 | NULL              |
| 8020001 | 802      |   3 | NULL              |
| 8020002 | 802      |   3 | NULL              |
| 8020003 | 802      |   3 | NULL              |
| 8030001 | 803      |   3 | NULL              |
| 8030002 | 803      |   3 | NULL              |
| 8030003 | 803      |   3 | NULL              |
| 8040001 | 804      |   3 | NULL              |
| 8040002 | 804      |   3 | NULL              |
| 8040003 | 804      |   3 | NULL              |
| 8050001 | 805      |   3 | NULL              |
| 8050002 | 805      |   3 | NULL              |
| 8050003 | 805      |   3 | NULL              |
| 8050004 | 805      |   3 | NULL              |
| 8050005 | 805      |   3 | NULL              |
| 8060001 | 806      |   3 | NULL              |
| 8060002 | 806      |   3 | NULL              |
+---------+----------+-----+-------------------+

Generating full path instead of linked hierarchy

If we are interested only in the full path N levels deep, we can omit ID and ParentID from the CTE. If we have a list of possible names in the supplementary table Names, it is easy to pick them from this table in CTE. The Names table should have enough rows for each level: 20 for level 1, 10 for level 2, 5 for level 3; 20+10+5 = 35 in total. It not necessary to have different sets of rows for each level, but it is easy to set it up properly, so I did it.

DECLARE @Names TABLE (Lvl int, Name nvarchar(4000), SeqNumber int);

-- First level: AAA, BBB, CCC, etc.
INSERT INTO @Names (Lvl, Name, SeqNumber)
SELECT 1, REPLICATE(CHAR(Number+64), 3) AS Name, Number AS SeqNumber
FROM Numbers
WHERE Number <= 20;

-- Second level: 001, 002, 003, etc.
INSERT INTO @Names (Lvl, Name, SeqNumber)
SELECT 2, REPLACE(STR(Number, 3), ' ', '0') AS Name, Number AS SeqNumber
FROM Numbers
WHERE Number <= 10;

-- Third level: I, II, III, IV, V
INSERT INTO @Names (Lvl, Name, SeqNumber) VALUES
(3, 'I',   1),
(3, 'II',  2),
(3, 'III', 3),
(3, 'IV',  4),
(3, 'V',   5);

SQL Fiddle Here is the final query. I split the FullPath into FilePath and FileName.

WITH
CTE
AS
(
    SELECT 
        TOP(CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 1)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            AS int))

        1 AS Lvl
        ,CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 2)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            AS int) AS ChildRowCount
        ,N.Name AS FullPath
        ,N.Name AS [FilePath]
        ,CAST(N'' AS nvarchar(4000)) AS [FileName]
    FROM
        Numbers
        INNER JOIN @Names AS N ON 
            N.SeqNumber = Numbers.Number AND N.Lvl = 1
    ORDER BY Numbers.Number

    UNION ALL

    SELECT
        CTE.Lvl + 1 AS Lvl
        ,CA.ChildRowCount
        ,CTE.FullPath + '\' + CA.Name AS FullPath

        ,CASE WHEN CA.ChildRowCount IS NOT NULL 
            THEN CTE.FullPath + '\' + CA.Name
            ELSE CTE.FullPath END AS [FilePath]

        ,CASE WHEN CA.ChildRowCount IS NULL 
            THEN CA.Name
            ELSE N'' END AS [FileName]
    FROM
        CTE
        CROSS APPLY
        (
            SELECT
                Numbers.Number
                ,CAST(
                    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
                    (
                    1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                      - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    )
                    + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    AS int) AS ChildRowCount
                ,N.Name
            FROM
                Numbers
                INNER JOIN @Names AS N ON 
                    N.SeqNumber = Numbers.Number AND N.Lvl = CTE.Lvl + 1
            WHERE Numbers.Number <= CTE.ChildRowCount
        ) AS CA
    WHERE
        CTE.ChildRowCount IS NOT NULL
)
SELECT
    CTE.FullPath
    ,CTE.[FilePath]
    ,CTE.[FileName]
FROM CTE
WHERE CTE.ChildRowCount IS NULL
ORDER BY FullPath;

Result

+-------------+----------+----------+
|  FullPath   | FilePath | FileName |
+-------------+----------+----------+
| AAA\001\I   | AAA\001  | I        |
| AAA\001\II  | AAA\001  | II       |
| AAA\002\I   | AAA\002  | I        |
| AAA\002\II  | AAA\002  | II       |
| AAA\002\III | AAA\002  | III      |
| AAA\002\IV  | AAA\002  | IV       |
| AAA\002\V   | AAA\002  | V        |
| AAA\003\I   | AAA\003  | I        |
| AAA\003\II  | AAA\003  | II       |
| AAA\003\III | AAA\003  | III      |
| AAA\004\I   | AAA\004  | I        |
| AAA\004\II  | AAA\004  | II       |
| AAA\004\III | AAA\004  | III      |
| AAA\004\IV  | AAA\004  | IV       |
| BBB\001\I   | BBB\001  | I        |
| BBB\001\II  | BBB\001  | II       |
| CCC\001\I   | CCC\001  | I        |
| CCC\001\II  | CCC\001  | II       |
| CCC\001\III | CCC\001  | III      |
| CCC\001\IV  | CCC\001  | IV       |
| CCC\001\V   | CCC\001  | V        |
| CCC\002\I   | CCC\002  | I        |
| CCC\003\I   | CCC\003  | I        |
| CCC\003\II  | CCC\003  | II       |
| CCC\004\I   | CCC\004  | I        |
| CCC\004\II  | CCC\004  | II       |
| CCC\005\I   | CCC\005  | I        |
| CCC\005\II  | CCC\005  | II       |
| CCC\005\III | CCC\005  | III      |
| CCC\006\I   | CCC\006  | I        |
| CCC\006\II  | CCC\006  | II       |
| CCC\006\III | CCC\006  | III      |
| CCC\006\IV  | CCC\006  | IV       |
| CCC\007\I   | CCC\007  | I        |
| CCC\007\II  | CCC\007  | II       |
| CCC\007\III | CCC\007  | III      |
| CCC\007\IV  | CCC\007  | IV       |
| CCC\008\I   | CCC\008  | I        |
| CCC\008\II  | CCC\008  | II       |
| CCC\008\III | CCC\008  | III      |
| CCC\009\I   | CCC\009  | I        |
| CCC\009\II  | CCC\009  | II       |
| CCC\009\III | CCC\009  | III      |
| CCC\009\IV  | CCC\009  | IV       |
| CCC\010\I   | CCC\010  | I        |
| CCC\010\II  | CCC\010  | II       |
| CCC\010\III | CCC\010  | III      |
| DDD\001\I   | DDD\001  | I        |
| DDD\001\II  | DDD\001  | II       |
| DDD\001\III | DDD\001  | III      |
| DDD\001\IV  | DDD\001  | IV       |
| DDD\002\I   | DDD\002  | I        |
| DDD\003\I   | DDD\003  | I        |
| DDD\003\II  | DDD\003  | II       |
| DDD\003\III | DDD\003  | III      |
| DDD\003\IV  | DDD\003  | IV       |
| DDD\004\I   | DDD\004  | I        |
| DDD\004\II  | DDD\004  | II       |
| DDD\004\III | DDD\004  | III      |
| DDD\005\I   | DDD\005  | I        |
| DDD\006\I   | DDD\006  | I        |
| DDD\006\II  | DDD\006  | II       |
| DDD\006\III | DDD\006  | III      |
| DDD\007\I   | DDD\007  | I        |
| DDD\007\II  | DDD\007  | II       |
| DDD\008\I   | DDD\008  | I        |
| DDD\008\II  | DDD\008  | II       |
| DDD\008\III | DDD\008  | III      |
| DDD\009\I   | DDD\009  | I        |
| DDD\009\II  | DDD\009  | II       |
| DDD\010\I   | DDD\010  | I        |
| DDD\010\II  | DDD\010  | II       |
| DDD\010\III | DDD\010  | III      |
| DDD\010\IV  | DDD\010  | IV       |
| DDD\010\V   | DDD\010  | V        |
| EEE\001\I   | EEE\001  | I        |
| EEE\001\II  | EEE\001  | II       |
| FFF\001\I   | FFF\001  | I        |
| FFF\002\I   | FFF\002  | I        |
| FFF\002\II  | FFF\002  | II       |
| FFF\003\I   | FFF\003  | I        |
| FFF\003\II  | FFF\003  | II       |
| FFF\003\III | FFF\003  | III      |
| FFF\003\IV  | FFF\003  | IV       |
| FFF\003\V   | FFF\003  | V        |
| FFF\004\I   | FFF\004  | I        |
| FFF\004\II  | FFF\004  | II       |
| FFF\004\III | FFF\004  | III      |
| FFF\004\IV  | FFF\004  | IV       |
| FFF\005\I   | FFF\005  | I        |
| FFF\006\I   | FFF\006  | I        |
| FFF\007\I   | FFF\007  | I        |
| FFF\007\II  | FFF\007  | II       |
| FFF\007\III | FFF\007  | III      |
| GGG\001\I   | GGG\001  | I        |
| GGG\001\II  | GGG\001  | II       |
| GGG\001\III | GGG\001  | III      |
| GGG\002\I   | GGG\002  | I        |
| GGG\003\I   | GGG\003  | I        |
| GGG\003\II  | GGG\003  | II       |
| GGG\003\III | GGG\003  | III      |
| GGG\004\I   | GGG\004  | I        |
| GGG\004\II  | GGG\004  | II       |
| HHH\001\I   | HHH\001  | I        |
| HHH\001\II  | HHH\001  | II       |
| HHH\001\III | HHH\001  | III      |
| HHH\002\I   | HHH\002  | I        |
| HHH\002\II  | HHH\002  | II       |
| HHH\002\III | HHH\002  | III      |
| HHH\002\IV  | HHH\002  | IV       |
| HHH\002\V   | HHH\002  | V        |
| HHH\003\I   | HHH\003  | I        |
| HHH\003\II  | HHH\003  | II       |
| HHH\003\III | HHH\003  | III      |
| HHH\003\IV  | HHH\003  | IV       |
| HHH\003\V   | HHH\003  | V        |
| HHH\004\I   | HHH\004  | I        |
| HHH\004\II  | HHH\004  | II       |
| HHH\004\III | HHH\004  | III      |
| HHH\004\IV  | HHH\004  | IV       |
| HHH\004\V   | HHH\004  | V        |
| HHH\005\I   | HHH\005  | I        |
| HHH\005\II  | HHH\005  | II       |
| HHH\005\III | HHH\005  | III      |
| HHH\005\IV  | HHH\005  | IV       |
| HHH\005\V   | HHH\005  | V        |
| HHH\006\I   | HHH\006  | I        |
| HHH\007\I   | HHH\007  | I        |
| HHH\007\II  | HHH\007  | II       |
| HHH\007\III | HHH\007  | III      |
| HHH\008\I   | HHH\008  | I        |
| HHH\008\II  | HHH\008  | II       |
| HHH\008\III | HHH\008  | III      |
| HHH\008\IV  | HHH\008  | IV       |
| HHH\008\V   | HHH\008  | V        |
+-------------+----------+----------+

So here is what I came up with. With the goal of creating a directory structure, I was looking for usable "names" for the directories and files. Because I was unable to get the TOP(n) working in the CROSS APPLYs (I think I attempted to correlate the queries by using a value from the parent as the n in the TOP(n) but then it wasn't random), I decided to create a type of "numbers" table that would allow an INNER JOIN or WHERE condition to produce a set of n elements simply by randomizing a number and specifying it as WHERE table.Level = random_number. The trick is that there is only 1 row for Level1, 2 rows for Level2, 3 rows for Level3, and so on. Hence, using WHERE LevelID = 3 will get me 3 rows, and each row has a value that I can use as a directory name.

SETUP

This part was originally specified inline, as part of the CTE. But for the sake of readability (so that you don't need to scroll through lots of INSERT statements to get to the few lines of the real query), I broke it out into a local temporary table.

IF (OBJECT_ID(N'tempdb..#Elements') IS NULL)
BEGIN
  PRINT 'Creating #Elements table...';
  CREATE TABLE #Elements (
     ElementLevel TINYINT NOT NULL,
     LevelName NVARCHAR(50) NOT NULL
                         );

  PRINT 'Populating #Elements table...';
  INSERT INTO #Elements (ElementLevel, LevelName)
    SELECT tmp.[Level], tmp.[Name]
    FROM (
                  SELECT 1,  N'Ella'
       UNION ALL  SELECT 2,  N'Itchy'
       UNION ALL  SELECT 2,  N'Scratchy'
       UNION ALL  SELECT 3,  N'Moe'
       UNION ALL  SELECT 3,  N'Larry'
       UNION ALL  SELECT 3,  N'Curly'
       UNION ALL  SELECT 4,  N'Ian'
       UNION ALL  SELECT 4,  N'Stephen'
       UNION ALL  SELECT 4,  N'Peter'
       UNION ALL  SELECT 4,  N'Bernard'
       UNION ALL  SELECT 5,  N'Michigan'
       UNION ALL  SELECT 5,  N'Erie'
       UNION ALL  SELECT 5,  N'Huron'
       UNION ALL  SELECT 5,  N'Ontario'
       UNION ALL  SELECT 5,  N'Superior'
       UNION ALL  SELECT 6,  N'White'
       UNION ALL  SELECT 6,  N'Orange'
       UNION ALL  SELECT 6,  N'Blonde'
       UNION ALL  SELECT 6,  N'Pink'
       UNION ALL  SELECT 6,  N'Blue'
       UNION ALL  SELECT 6,  N'Brown'
       UNION ALL  SELECT 7,  N'Asia'
       UNION ALL  SELECT 7,  N'Africa'
       UNION ALL  SELECT 7,  N'North America'
       UNION ALL  SELECT 7,  N'South America'
       UNION ALL  SELECT 7,  N'Antarctica'
       UNION ALL  SELECT 7,  N'Europe'
       UNION ALL  SELECT 7,  N'Australia'
       UNION ALL  SELECT 8,  N'AA'
       UNION ALL  SELECT 8,  N'BB'
       UNION ALL  SELECT 8,  N'CC'
       UNION ALL  SELECT 8,  N'DD'
       UNION ALL  SELECT 8,  N'EE'
       UNION ALL  SELECT 8,  N'FF'
       UNION ALL  SELECT 8,  N'GG'
       UNION ALL  SELECT 8,  N'HH'
       UNION ALL  SELECT 9,  N'I'
       UNION ALL  SELECT 9,  N'II'
       UNION ALL  SELECT 9,  N'III'
       UNION ALL  SELECT 9,  N'IV'
       UNION ALL  SELECT 9,  N'V'
       UNION ALL  SELECT 9,  N'VI'
       UNION ALL  SELECT 9,  N'VII'
       UNION ALL  SELECT 9,  N'VIII'
       UNION ALL  SELECT 9,  N'IX'
       UNION ALL  SELECT 10, N'Million'
       UNION ALL  SELECT 10, N'Billion'
       UNION ALL  SELECT 10, N'Trillion'
       UNION ALL  SELECT 10, N'Quadrillion'
       UNION ALL  SELECT 10, N'Quintillion'
       UNION ALL  SELECT 10, N'Sestillion'
       UNION ALL  SELECT 10, N'Sextillion'
       UNION ALL  SELECT 10, N'Octillion'
       UNION ALL  SELECT 10, N'Nonillion'
       UNION ALL  SELECT 10, N'Decillion'
     ) tmp([Level], [Name]);
END;

MAIN QUERY

For Level 1 I just grabbed [name] values out of sys.objects since there are always plenty of rows there. But, if I needed more control over the names, I could just expand the #Elements table to contain additional levels.

;WITH topdir(Level1, Randy) AS
(
    SELECT TOP ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 20) + 5 ) so.[name],
                ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 10) + 1 )
    FROM sys.objects so
    ORDER BY CRYPT_GEN_RANDOM(8) ASC
)
SELECT  td.Level1, tmp1.Level2, tmp2.Level3
FROM    topdir td
CROSS APPLY (SELECT help.LevelName, (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 5) + 1
            FROM #Elements help
            WHERE help.ElementLevel = td.Randy
            ) tmp1 (Level2, Bandy)
CROSS APPLY (SELECT help.LevelName
            FROM #Elements help
            WHERE help.ElementLevel = tmp1.Bandy
            ) tmp2 (Level3);

QUERY ADAPTED TO PRODUCE EACH FILE'S PATH, NAME, and CONTENTS

In order to generate the full paths for the files and the file contents, I made the main SELECT of the CTE just another CTE and added a new main SELECT that gave the proper outputs that simply need to go into files.

DECLARE @Template NVARCHAR(4000);
SET @Template = N'<?xml version="1.0" encoding="ISO-8859-1"?>
<ns0:P4131 xmlns:ns0="http://switching/xi">
<R000000>
    <R00000010>R000000</R00000010>
    <R00000020>I</R00000020>
    <R00000030>{{Tag30}}</R00000030>
    <R00000040>{{Tag40}}</R00000040>
    <R00000050>{{Tag50}}</R00000050>
    <R00000060>2</R00000060>
</R000000>
</ns0:P4131>
';


;WITH topdir(Level1, Thing1) AS
(
    SELECT TOP ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 20) + 5 ) so.[name],
                ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 10) + 1 )
    FROM sys.objects so
    ORDER BY CRYPT_GEN_RANDOM(8) ASC
), main AS
(
   SELECT  td.Level1, tmp1.Level2, tmp2.Level3,
           td.Level1 + N'\' + tmp1.Level2 AS [FullPath],
           RIGHT('000' + CONVERT(VARCHAR(10),
                          (CONVERT(INT, CRYPT_GEN_RANDOM(2)) % 9999) + 1), 4) AS [R30],
           RIGHT('000' + CONVERT(VARCHAR(10),
                          (CONVERT(INT, CRYPT_GEN_RANDOM(2)) % 500) + 100), 4) AS [R50],
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [RowNum]
   FROM    topdir td
   CROSS APPLY (SELECT help.LevelName, (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 5) + 1
                FROM #Elements help
                WHERE help.ElementLevel = td.Thing1
               ) tmp1 (Level2, Thing2)
   CROSS APPLY (SELECT help.LevelName
                FROM #Elements help
                WHERE help.ElementLevel = tmp1.Thing2
               ) tmp2 (Level3)
)
SELECT  mn.FullPath,
        mn.Level3 + N'.xml' AS [FileName],
        REPLACE(
            REPLACE(
                REPLACE(
                    @Template,
                    N'{{Tag30}}',
                    mn.R30),
                N'{{Tag40}}',
                mn.RowNum),
            N'{{Tag50}}',
            mn.R50) AS [Contents]
FROM    main mn;

EXTRA CREDIT

While not part of the requirements stated in the question, the goal (which was mentioned) was to create files to test recursive File System functions with. So how do we take this result set of path names, file names, and file contents and do something with it? We just need two SQLCLR functions: one to create the folders and one to create the files.

In order to make this data functional, I modified the main SELECT of the CTE shown directly above as follows:

SELECT  SQL#.File_CreateDirectory(
            N'C:\Stuff\TestXmlFiles\' + mn.FullPath) AS [CreateTheDirectory],
        SQL#.File_WriteFile(
            N'C:\Stuff\TestXmlFiles\' + mn.FullPath + N'\' + mn.Level3 + N'.xml',
            REPLACE(
                REPLACE(
                    REPLACE(
                        @Template,
                        N'{{Tag30}}',
                        mn.R30),
                    N'{{Tag40}}',
                    mn.RowNum),
                N'{{Tag50}}',
                mn.R50), -- @FileData
            0, -- @AppendData
            '' -- @FileEncoding
                            ) AS [WriteTheFile]
FROM    main mn;