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

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 303
    Comment on it

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

    alt text

    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.
    

 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: