Every XML has two elements i.e.
1) Attributes and
2) Value

SELECT magazineId AS '@id'
    , magazineName AS '@name'
    , CoverPage AS '@coverPage'
    , isActive AS '@status'
    , publishDate AS '@publishDate'
    , expiryDate AS '@expiryDate'
    ,(
        SELECT
                -- Map columns to XML attributes/elements with XPath selectors.
                chapterId  AS '@id',
                chapterName AS '@name',
                chapterOrder AS '@Order',                   
                (
                    -- Use a sub query for child elements.
                    SELECT
                        pageid AS '@id',
                        pageName AS '@name'                         
                        FROM tbl_page
                        WHERE chapterId = tbl_chapter.chapterId                         
                        FOR XML PATH('page'), -- The element name for each row.
                        TYPE -- Column is typed so it nests as XML, not text.                            
                ) -- The root element name for this child collection.
            FROM tbl_chapter
            WHERE magazineId = tbl_magazine.magazineId  
            FOR XML PATH('chapter'), -- The element name for each row.
            TYPE
    )    
FROM tbl_magazine
FOR XML PATH('magazine') -- The element name for each row.
,ROOT('Magzines')  -- The root element name for this result set.