By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @xml XML='<Reservation><Name>Neal</Name><Flight>12</Flight><Flight>34</Flight><Flight>56</Flight></Reservation>';
DECLARE @xmlTable TABLE (
xmlDoc Xml
);
Insert into @xmltable values (@xml)
Select xmlDoc from @XmlTable
Select xmlDoc.value('(//Name[1]/text())[1]', 'varchar(100)') as Passenger,
XmlData2.xmlDoc2.query('.'),
XmlData2.xmlDoc2.value('.', 'int')
FROM @xmlTable as t
CROSS APPLY
t.xmlDoc.nodes('//Flight') AS XmlData2(xmlDoc2)
| xmlDoc |
|---|
| <Reservation><Name>Neal</Name><Flight>12</Flight><Flight>34</Flight><Flight>56</Flight></Reservation> |
| Passenger | (No column name) | (No column name) |
|---|---|---|
| Neal | <Flight>12</Flight> | 12 |
| Neal | <Flight>34</Flight> | 34 |
| Neal | <Flight>56</Flight> | 56 |