Following is the XML which we want to create:

SELECT magazineId
    , magazineName
    , coverPage
    , publishDate
    , expiryDate
    , isActive 
    ,( 
        SELECT (  
                 SELECT (           
                            SELECT chapterId
                                    , chapterName
                                    , chapterOrder
                                    ,                       
                                    (
                                        SELECT 
                                        ( 
                                            ( 
                                                SELECT pg.pageId
                                                    , pg.pageName
                                                    FROM tbl_page pg 
                                                    WHERE pg.chapterId = tbl_chapter.chapterId 
                                                    ORDER BY pg.pageOrder ASC
                                                    FOR XML PATH('Page'),
                                                    TYPE 
                                            )
                                        )
                                        FOR XML PATH('Pages'), 
                                        TYPE
                                    )
                                FROM tbl_chapter 
                                WHERE magazineId = tbl_magazine.magazineId
                                FOR XML PATH('Chapter'), -- The element name for each row.
                                TYPE
                        )
                 FOR XML PATH(''),
                 TYPE
                )
        FOR XML PATH('Chapters'), -- The element name for each row.
        TYPE    
    )
FROM tbl_magazine
FOR XML PATH('') -- The element name for each row.
,ROOT('Magzines')  -- The root element name for this result set.