April 7, 2015 at 3:36 am
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
April 7, 2015 at 6:06 am
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
April 7, 2015 at 6:12 am
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