Generate XML path format from SQL table

Question

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!

If i specify the alias different i will get under FlightDescription <Number>F0123</Number><Number2>F0789</Number2> and i dont want that, each number should be a different flight description. — Giuseppe Lolli
– Giuseppe Lolli, Commented Feb 10, 2020 at 9:49

lptr · Accepted Answer · 2020-02-10 10:01:50Z

2

Create an empty node between the two FlightDescription/Number elements/nodes.

declare @f table
(
Id int,
Flight1 varchar(20),
Flight2 varchar(20)
);

insert into @f(Id, Flight1, Flight2)
values
(1, 'F0123', 'F0789'),
(2, 'F0345', 'F0678');

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

answered Feb 10, 2020 at 10:01

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Giuseppe Lolli Over a year ago

Hi man, what if i want to merge the <FlightDescription> with inside the 2 flight numbers? i would like to have: <FlightDescription> <Number>F0123</Number> <Number>F0789</Number> </FlightDescription>

lptr Over a year ago

then you create the empty node at the FlightDescription level : '' AS 'FlightDescription',

Yitzhak Khabinsky · Accepted Answer · 2020-02-10 15:51:05Z

1

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>

answered Feb 10, 2020 at 15:51

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

3 Comments

lptr Over a year ago

check the execution plan and the excessive memory grant...why create an xml from data and xquery on it and not simply create the xml directly from the data?

Yitzhak Khabinsky Over a year ago

I completely understand what you are saying. We are on the same page. However, slight modification, and the OP is complete lost. It is not always just about performance.

Yitzhak Khabinsky Over a year ago

lptr, please connect with me on the LinkedIn

Collectives™ on Stack Overflow

Generate XML path format from SQL table

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related