How to populate more than one ComplexType XML elements from SQL?

  • Hello and apologies for a bit open question.

    I need to build an XML file with three sibling ComplexType elements, e.g. finance, itinerary and crew details. I can separately build each of them

    with no problem at all. Below is an example of the finance part, but I need one's advice how to have three within the same XML, please?

    Are there any examples I can learn from?

    What I have found so far was only to build one ComplexType, as I did below, but not a combination of them.

    Much obliged !

    WITH finance AS

    (SELECT 200.12 AS fare,

    12.78 AS VAT,

    45.5 AS Tax

    UNION ALL

    SELECT 150.12 AS fare,

    10.78 AS VAT,

    35.5 AS Tax)

    SELECT a.currency AS '@currency',

    a.crewid AS '@crewid',

    fare,

    VAT,

    Tax

    FROM finance

    CROSS JOIN (SELECT 'AUD' AS currency,

    1 AS crewid) a

    FOR XML PATH('perpax'), ROOT('financials'),ELEMENTS

  • A bit more details we need to answer. As a starting point try subqueries

    WITH finance AS

    (SELECT 200.12 AS fare,

    12.78 AS VAT,

    45.5 AS Tax

    UNION ALL

    SELECT 150.12 AS fare,

    10.78 AS VAT,

    35.5 AS Tax)

    SELECT a.currency AS '@currency', a.crewid AS '@crewid' ,

    (SELECT

    fare

    FOR XML PATH('sibling1'),TYPE) ,

    (SELECT a.currency AS '@currency',

    VAT

    FOR XML PATH('sibling2'),TYPE) ,

    (SELECT a.crewid AS '@crewid',

    fare,

    VAT,

    Tax

    FOR XML PATH('sibling3'),TYPE)

    FROM finance

    CROSS JOIN (SELECT 'AUD' AS currency,

    1 AS crewid) a

    FOR XML PATH('root'),ELEMENTS

  • Looks like a perfect start to me!

    Thank you!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply