Generate XML path format from SQL table

I was trying to get an XML from a SQL table:

My Table

------------
-- Table1 --
------------

ID    Flight1    Flight2
--------------------------
1     F0123      F0789
2     F0345      F0678

Query:

Select
    A.[ID],
    A.[Flight1] as "FlightDescription/Number",
    A.[Flight2] as "FlightDescription/Number"
from
    [Table1] A
for xml path('Flight'), root('Flights')

My expected result is:

 <Flights>
  <Flight>
    <ID>1</ID>
    <FlightDescription>
        <Number>F0123</Number>
    </FlightDescription>
    <FlightDescription>
        <Number>F0789</Number>
    </FlightDescription>
  </Flight>
  <Flight>
    <ID>2</ID>
    <FlightDescription>
        <Number>F0345</Number>
    </FlightDescription>
    <FlightDescription>
        <Number>F0678</Number>
    </FlightDescription>
  </Flight>
</Flights>

But instead of that I’m getting this:

<Flights>
  <Flight>
    <ID>1</ID>
    <FlightDescription>
        <Number>F0123F0789</Number>
    </FlightDescription>
  </Flight>
  <Flight>
    <ID>2</ID>
    <FlightDescription>
        <Number>F0345F0678</Number>
    </FlightDescription>
  </Flight>
</Flights>

I can figure it out… In my table i have more than 4 flight numbers for each ID so i would like to know if there is a way to have all of them in the way i need.

Thanks in advance!

Answer

Here is another slightly more elaborate way. XQuery and its FLWOR expression. This way you shape your XML explicitly. It is easy to create, understand, make any modifications, and maintain down the road.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT PRIMARY KEY, Flight1 VARCHAR(20), Flight2 VARCHAR(20));

INSERT INTO @tbl (ID, Flight1, Flight2) VALUES
(1, 'F0123', 'F0789'),
(2, 'F0345', 'F0678');
-- DDL and sample data population, end

SELECT (
SELECT * 
FROM @tbl
FOR XML PATH('r'), TYPE, ROOT('root')).query('<Flights>
{
    for $x in /root/r
    return <Flight>
        {$x/ID}
        <FlightDescription>
            <number>{data($x/Flight1)}</number>
            <number>{data($x/Flight2)}</number>
        </FlightDescription>
    </Flight>
}
</Flights>'
);

Output

<Flights>
  <Flight>
    <ID>1</ID>
    <FlightDescription>
      <number>F0123</number>
      <number>F0789</number>
    </FlightDescription>
  </Flight>
  <Flight>
    <ID>2</ID>
    <FlightDescription>
      <number>F0345</number>
      <number>F0678</number>
    </FlightDescription>
  </Flight>
</Flights>