Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL SERVER - Generating XML - 1

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 280
    Comment on it

    Following is the XML which we want to create:

    sample xml 1

    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.
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: