SQL Server 2016: resultados de la consulta en formato JSON

Prueba la siguiente solución:

DECLARE @SourceTable TABLE (
    Level1_Name NVARCHAR(50) NOT NULL,
    Level1_Size INT NOT NULL,
    Level2_Name NVARCHAR(50) NOT NULL,
    Level2_Size INT NOT NULL,
    Content     NVARCHAR(100) NOT NULL
);
INSERT  @SourceTable
VALUES  
('Leadership', 270, 'Solutions', 70,    'Book #1'),
('Leadership', 270, 'Solutions', 70,    'Book #2'),
('Leadership', 270, 'Strategy', 121,    'Book #3'), 
('Leadership', 270, 'Strategy', 121,    'Book #4'), 
('Leadership', 270, 'Strategy', 121,    'Book #5'), 
('Leadership', 270, 'Development', 10,  'Book #6'), 
('Coco Jambo', 111, 'Solutions', 111, 'Book #111');

SELECT 
    name    = 'Root object',
    children= (
        SELECT  DISTINCT 
            name    = lvl1.Level1_Name, 
            size    = lvl1.Level1_Size,
            children= (
                SELECT  DISTINCT 
                    name    = lvl2.Level2_Name, 
                    size    = lvl2.Level2_Size,
                    children= (
                        SELECT  DISTINCT 
                            name    = lvl3.Content 
                        FROM    @SourceTable lvl3
                        WHERE   lvl2.Level1_Name = lvl1.Level1_Name 
                        AND     lvl3.Level2_Name = lvl2.Level2_Name
                        FOR JSON PATH
                    )
                FROM    @SourceTable lvl2
                WHERE   lvl2.Level1_Name = lvl1.Level1_Name
                FOR JSON PATH
            )
        FROM    @SourceTable lvl1
        FOR JSON PATH
    )
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Resultados:

{
  "name": "Root object",
  "children": [
    {
      "name": "Leadership",
      "size": 270,
      "children": [
        {
          "name": "Development",
          "size": 10,
          "children": [
            {
              "name": "Book #6"
            }
          ]
        },
        {
          "name": "Solutions",
          "size": 70,
          "children": [
            {
              "name": "Book #1"
            },
            {
              "name": "Book #111"
            },
            {
              "name": "Book #2"
            }
          ]
        },
 ...

Manifestación