TSQL For XML JSON AUTO generates a flat result with using CTE with UNION

Question

For the following TSQL, the expectation is to generate and output where COL is nested inside T, but returns a flat result.

With FilteredTables as (
    select * from INFORMATION_SCHEMA.TABLES
    union
    select * from INFORMATION_SCHEMA.TABLES -- Repeated just to show the issue
)
select T.TABLE_SCHEMA, T.TABLE_NAME,
    COL.COLUMN_NAME, --as "columns.name",
    COL.DATA_TYPE --as "columns.type"
    from FilteredTables T
    INNER JOIN INFORMATION_SCHEMA.COLUMNS COL
        on T.TABLE_SCHEMA = COL.TABLE_SCHEMA
        and T.TABLE_NAME = COL.TABLE_NAME
--order by [Schema], [Table]
FOR XML Auto

When the union is removed from the FilteredTables CTE it works as expected

Is this a SQL Bug or is there any good reason for this?

Answer 1

In case a Union is required instead of using FROM cte_name, use FROM (SELECT * FROM cte_name)

With FilteredTables as (
    select * from INFORMATION_SCHEMA.TABLES
    union
    select * from INFORMATION_SCHEMA.TABLES -- Repeated just to show the issue
)
select T.TABLE_SCHEMA, T.TABLE_NAME,
    COL.COLUMN_NAME, --as "columns.name",
    COL.DATA_TYPE --as "columns.type"
    from 
       -- Workaround  
       (SELECT * FROM FilteredTables) T

    INNER JOIN INFORMATION_SCHEMA.COLUMNS COL
        on T.TABLE_SCHEMA = COL.TABLE_SCHEMA
        and T.TABLE_NAME = COL.TABLE_NAME
--order by [Schema], [Table]
FOR XML Auto