搜索
搜索

XML 路径，从表中提取数据并合并节点

Giuseppe Lolli 发表于 Dev
25
朱塞佩·洛利

我试图从我的表中获取特定格式的 XML 文件。

我的表是下一个：

IF OBJECT_ID('TripHistoryTest','U') IS NOT NULL DROP TABLE TripHistoryTest
create table TripHistoryTest
(
    [BarCode] nvarchar(100),
    [PassengerName] nvarchar(100),
    [SegmentType] char(1),
    [Flight1_From] nvarchar(100),
    [Flight1_FromTime] nvarchar(100),
    [Flight1_To] nvarchar(100),
    [Flight1_ToTime] nvarchar(100),
    [Flight2_From] nvarchar(100),
    [Flight2_FromTime] nvarchar(100),
    [Flight2_To] nvarchar(100),
    [Flight2_ToTime] nvarchar(100),
    [Flight3_From] nvarchar(100),
    [Flight3_FromTime] nvarchar(100),
    [Flight3_To] nvarchar(100),
    [Flight3_ToTime] nvarchar(100),
    [Flight4_From] nvarchar(100),
    [Flight4_FromTime] nvarchar(100),
    [Flight4_To] nvarchar(100),
    [Flight4_ToTime] nvarchar(100),
    [Hotel_From] nvarchar(100),
    [Hotel_FromTime] nvarchar(100),
    [Hotel_To] nvarchar(100),
    [Hotel_ToTime] nvarchar(100),
    [Train_From] nvarchar(100),
    [Train_FromTime] nvarchar(100),
    [Train_To] nvarchar(100),
    [Train_ToTime] nvarchar(100),
    [Car_From] nvarchar(100),
    [Car_FromTime] nvarchar(100),
    [Car_To] nvarchar(100),
    [Car_ToTime] nvarchar(100)
)

insert into TripHistoryTest
(
    [BarCode],
    [PassengerName],
    [SegmentType],
    [Flight1_From],
    [Flight1_FromTime] ,
    [Flight1_To] ,
    [Flight1_ToTime] ,
    [Flight2_From],
    [Flight2_FromTime],
    [Flight2_To],
    [Flight2_ToTime],
    [Flight3_From],
    [Flight3_FromTime],
    [Flight3_To],
    [Flight3_ToTime],
    [Flight4_From],
    [Flight4_FromTime],
    [Flight4_To],
    [Flight4_ToTime],
    [Hotel_From],
    [Hotel_FromTime],
    [Hotel_To],
    [Hotel_ToTime],
    [Train_From] ,
    [Train_FromTime] ,
    [Train_To] ,
    [Train_ToTime] ,
    [Car_From],
    [Car_FromTime],
    [Car_To],
    [Car_ToTime] 
)
select 
    'BR666999', 'KETYM PORTA', 'F', 
    'MILAN', '06:00', 'NAPOLI', '07:00',
    'NAPOLI', '08:00', 'BUDAPEST', '10:00',
    'BUDAPEST', '11:00', 'CRAIOVA', '13:00',
    'CRAIOVA', '14:00', 'ROMA', '16:00', 
    '','','','',
    '','','','',
    '','','',''
UNION ALL
select 
    'BR666999', 'KETYM PORTA', 'F', 
    'ROMA', '17:00', 'TORINO', '18:00',
    'TORINO', '19:00', 'MADRID', '20:00',
    '','','','',
    '','','','', 
    '','','','',
    '','','','',
    '','','',''
UNION ALL
select 
    'BR666999', 'KETYM PORTA', 'F', 
    'MADRID', '21:00', 'MANILA', '23:00',
    '','','','',
    '','','','',
    '','','','','','','','','','','','','','','',''
UNION ALL
select 
    'BR666999', 'KETYM PORTA', 'T', 
    '', '', '', '',
    '', '', '', '',
    '', '', '', '',
    '', '', '', '', 
    '','','','',
    'MANILA','23:30','CALBAYOG','23:40',
    '','','',''
UNION ALL
select 
    'BR666999', 'KETYM PORTA', 'H', 
    '', '', '', '',
    '', '', '', '',
    '', '', '', '',
    '', '', '', '', 
    'LASTHOPEHOTEL','23:50','LASTHOPEHOTEL','06:00',
    '','','','',
    '','','',''
UNION ALL
select 
    'BR666999', 'KETYM PORTA', 'C', 
    '', '', '', '',
    '', '', '', '',
    '', '', '', '',
    '', '', '', '', 
    '','','','',
    '','','','',
    'CALBAYOG','07:00','MARIANA TRENCH',''

我正在模拟我现在系统中的内容，所以这里有一些注意事项：

细分类型：

F：航班
T：火车
C：汽车
H：酒店

我使用 XML PATH 进行了查询但没有成功，这是我的输出：

select
    -- ###############
    -- ### BARCODE ###
    -- ###############
    A.[BarCode],
    -- ################
    -- ### TRAVELER ###
    -- ################
    (
        select distinct
            B.[PassengerName]
        from
            TripHistoryTest B
        where   1=1
                and A.[BarCode] = B.[BarCode]
        group by B.[PassengerName]
        for xml path ('Passenger'), root('PassengerList'), TYPE
    ),
    -- ###################
    -- ### AIR SEGMENT ###
    -- ###################
    (
        select distinct
            case when C.[Flight1_From] = '' then null else C.[Flight1_From] end as "AirSeg/Flight_From",
            case when C.[Flight1_From] = '' then null else C.[Flight1_FromTime] end as "AirSeg/Flight_FromTime",
            case when C.[Flight1_To] = '' then null else C.[Flight1_To] end as "AirSeg/Flight_To",
            case when C.[Flight1_To] = '' then null else C.[Flight1_ToTime] end as "AirSeg/Flight_ToTime",
            '',
            case when C.[Flight2_From] = '' then null else C.[Flight2_From] end as "AirSeg/Flight_From",
            case when C.[Flight2_From] = '' then null else C.[Flight2_FromTime] end as "AirSeg/Flight_FromTime",
            case when C.[Flight2_To] = '' then null else C.[Flight2_To] end as "AirSeg/Flight_To",
            case when C.[Flight2_To] = '' then null else C.[Flight2_ToTime] end as "AirSeg/Flight_ToTime",
            '',
            case when C.[Flight3_From] = '' then null else C.[Flight3_From] end as "AirSeg/Flight_From",
            case when C.[Flight3_From] = '' then null else C.[Flight3_FromTime] end as "AirSeg/Flight_FromTime",
            case when C.[Flight3_To] = '' then null else C.[Flight3_To] end as "AirSeg/Flight_To",
            case when C.[Flight3_To] = '' then null else C.[Flight3_ToTime] end as "AirSeg/Flight_ToTime",
            '',
            case when C.[Flight4_From] = '' then null else C.[Flight4_From] end as "AirSeg/Flight_From",
            case when C.[Flight4_From] = '' then null else C.[Flight4_FromTime] end as "AirSeg/Flight_FromTime",
            case when C.[Flight4_To] = '' then null else C.[Flight4_To] end as "AirSeg/Flight_To",
            case when C.[Flight4_To] = '' then null else C.[Flight4_ToTime] end as "AirSeg/Flight_ToTime"
        from
            TripHistoryTest C
        where   1=1
                and A.[BarCode] = C.[BarCode]
                and C.[SegmentType] = 'F'
        group by 
            C.[Flight1_From], C.[Flight1_FromTime], C.[Flight2_From], C.[Flight2_FromTime], C.[Flight3_From], C.[Flight3_FromTime],
            C.[Flight4_From], C.[Flight4_FromTime], C.[Flight1_To], C.[Flight1_ToTime], C.[Flight2_To], C.[Flight2_ToTime], C.[Flight3_To], C.[Flight3_ToTime],
            C.[Flight4_To], C.[Flight4_ToTime]
        for xml path ('Segment_List'), TYPE 
    )
from 
    TripHistoryTest A
group by
    A.[BarCode], A.[PassengerName], A.[Flight1_From], A.[Flight1_FromTime], A.[Flight2_From], A.[Flight2_FromTime], A.[Flight3_From], A.[Flight3_FromTime],
    A.[Flight4_From], A.[Flight4_FromTime], A.[Flight1_To], A.[Flight1_ToTime], A.[Flight2_To], A.[Flight2_ToTime], A.[Flight3_To], A.[Flight3_ToTime],
    A.[Flight4_To], A.[Flight4_ToTime]
order by A.[Flight1_FromTime] asc
for xml path ('BARCODE'), root ('BARCODE_List')

我的最终输出是：

<BARCODE_List>
  <BARCODE>
    <BarCode>BR666999</BarCode>
    <PassengerList>
      <Passenger>
        <PassengerName>KETYM PORTA</PassengerName>
      </Passenger>
    </PassengerList>
    <Segment_List>
      <AirSeg>
        <Flight_From>MADRID</Flight_From>
        <Flight_FromTime>21:00</Flight_FromTime>
        <Flight_To>MANILA</Flight_To>
        <Flight_ToTime>23:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>MILAN</Flight_From>
        <Flight_FromTime>06:00</Flight_FromTime>
        <Flight_To>NAPOLI</Flight_To>
        <Flight_ToTime>07:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>NAPOLI</Flight_From>
        <Flight_FromTime>08:00</Flight_FromTime>
        <Flight_To>BUDAPEST</Flight_To>
        <Flight_ToTime>10:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>BUDAPEST</Flight_From>
        <Flight_FromTime>11:00</Flight_FromTime>
        <Flight_To>CRAIOVA</Flight_To>
        <Flight_ToTime>13:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>CRAIOVA</Flight_From>
        <Flight_FromTime>14:00</Flight_FromTime>
        <Flight_To>ROMA</Flight_To>
        <Flight_ToTime>16:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>ROMA</Flight_From>
        <Flight_FromTime>17:00</Flight_FromTime>
        <Flight_To>TORINO</Flight_To>
        <Flight_ToTime>18:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>TORINO</Flight_From>
        <Flight_FromTime>19:00</Flight_FromTime>
        <Flight_To>MADRID</Flight_To>
        <Flight_ToTime>20:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
  </BARCODE>
  <BARCODE>
    <BarCode>BR666999</BarCode>
    <PassengerList>
      <Passenger>
        <PassengerName>KETYM PORTA</PassengerName>
      </Passenger>
    </PassengerList>
    <Segment_List>
      <AirSeg>
        <Flight_From>MADRID</Flight_From>
        <Flight_FromTime>21:00</Flight_FromTime>
        <Flight_To>MANILA</Flight_To>
        <Flight_ToTime>23:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>MILAN</Flight_From>
        <Flight_FromTime>06:00</Flight_FromTime>
        <Flight_To>NAPOLI</Flight_To>
        <Flight_ToTime>07:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>NAPOLI</Flight_From>
        <Flight_FromTime>08:00</Flight_FromTime>
        <Flight_To>BUDAPEST</Flight_To>
        <Flight_ToTime>10:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>BUDAPEST</Flight_From>
        <Flight_FromTime>11:00</Flight_FromTime>
        <Flight_To>CRAIOVA</Flight_To>
        <Flight_ToTime>13:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>CRAIOVA</Flight_From>
        <Flight_FromTime>14:00</Flight_FromTime>
        <Flight_To>ROMA</Flight_To>
        <Flight_ToTime>16:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>ROMA</Flight_From>
        <Flight_FromTime>17:00</Flight_FromTime>
        <Flight_To>TORINO</Flight_To>
        <Flight_ToTime>18:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>TORINO</Flight_From>
        <Flight_FromTime>19:00</Flight_FromTime>
        <Flight_To>MADRID</Flight_To>
        <Flight_ToTime>20:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
  </BARCODE>
  <BARCODE>
    <BarCode>BR666999</BarCode>
    <PassengerList>
      <Passenger>
        <PassengerName>KETYM PORTA</PassengerName>
      </Passenger>
    </PassengerList>
    <Segment_List>
      <AirSeg>
        <Flight_From>MADRID</Flight_From>
        <Flight_FromTime>21:00</Flight_FromTime>
        <Flight_To>MANILA</Flight_To>
        <Flight_ToTime>23:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>MILAN</Flight_From>
        <Flight_FromTime>06:00</Flight_FromTime>
        <Flight_To>NAPOLI</Flight_To>
        <Flight_ToTime>07:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>NAPOLI</Flight_From>
        <Flight_FromTime>08:00</Flight_FromTime>
        <Flight_To>BUDAPEST</Flight_To>
        <Flight_ToTime>10:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>BUDAPEST</Flight_From>
        <Flight_FromTime>11:00</Flight_FromTime>
        <Flight_To>CRAIOVA</Flight_To>
        <Flight_ToTime>13:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>CRAIOVA</Flight_From>
        <Flight_FromTime>14:00</Flight_FromTime>
        <Flight_To>ROMA</Flight_To>
        <Flight_ToTime>16:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>ROMA</Flight_From>
        <Flight_FromTime>17:00</Flight_FromTime>
        <Flight_To>TORINO</Flight_To>
        <Flight_ToTime>18:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>TORINO</Flight_From>
        <Flight_FromTime>19:00</Flight_FromTime>
        <Flight_To>MADRID</Flight_To>
        <Flight_ToTime>20:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
  </BARCODE>
  <BARCODE>
    <BarCode>BR666999</BarCode>
    <PassengerList>
      <Passenger>
        <PassengerName>KETYM PORTA</PassengerName>
      </Passenger>
    </PassengerList>
    <Segment_List>
      <AirSeg>
        <Flight_From>MADRID</Flight_From>
        <Flight_FromTime>21:00</Flight_FromTime>
        <Flight_To>MANILA</Flight_To>
        <Flight_ToTime>23:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>MILAN</Flight_From>
        <Flight_FromTime>06:00</Flight_FromTime>
        <Flight_To>NAPOLI</Flight_To>
        <Flight_ToTime>07:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>NAPOLI</Flight_From>
        <Flight_FromTime>08:00</Flight_FromTime>
        <Flight_To>BUDAPEST</Flight_To>
        <Flight_ToTime>10:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>BUDAPEST</Flight_From>
        <Flight_FromTime>11:00</Flight_FromTime>
        <Flight_To>CRAIOVA</Flight_To>
        <Flight_ToTime>13:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>CRAIOVA</Flight_From>
        <Flight_FromTime>14:00</Flight_FromTime>
        <Flight_To>ROMA</Flight_To>
        <Flight_ToTime>16:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
    <Segment_List>
      <AirSeg>
        <Flight_From>ROMA</Flight_From>
        <Flight_FromTime>17:00</Flight_FromTime>
        <Flight_To>TORINO</Flight_To>
        <Flight_ToTime>18:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>TORINO</Flight_From>
        <Flight_FromTime>19:00</Flight_FromTime>
        <Flight_To>MADRID</Flight_To>
        <Flight_ToTime>20:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
  </BARCODE>
</BARCODE_List>

但是，这不是我所期望的，我想要这样的东西：

<BARCODE_List>
  <BARCODE>
    <BarCode>BR666999</BarCode>
    <PassengerList>
      <Passenger>
        <PassengerName>KETYM PORTA</PassengerName>
      </Passenger>
    </PassengerList>
    <Segment_List>
      <AirSeg>
        <Flight_From>MILAN</Flight_From>
        <Flight_FromTime>06:00</Flight_FromTime>
        <Flight_To>NAPOLI</Flight_To>
        <Flight_ToTime>07:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>NAPOLI</Flight_From>
        <Flight_FromTime>08:00</Flight_FromTime>
        <Flight_To>BUDAPEST</Flight_To>
        <Flight_ToTime>10:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>BUDAPEST</Flight_From>
        <Flight_FromTime>11:00</Flight_FromTime>
        <Flight_To>CRAIOVA</Flight_To>
        <Flight_ToTime>13:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>CRAIOVA</Flight_From>
        <Flight_FromTime>14:00</Flight_FromTime>
        <Flight_To>ROMA</Flight_To>
        <Flight_ToTime>16:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>ROMA</Flight_From>
        <Flight_FromTime>17:00</Flight_FromTime>
        <Flight_To>TORINO</Flight_To>
        <Flight_ToTime>18:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>TORINO</Flight_From>
        <Flight_FromTime>19:00</Flight_FromTime>
        <Flight_To>MADRID</Flight_To>
        <Flight_ToTime>20:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>MADRID</Flight_From>
        <Flight_FromTime>21:00</Flight_FromTime>
        <Flight_To>MANILA</Flight_To>
        <Flight_ToTime>23:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
  </BARCODE>
</BARCODE_List>

有没有一种简单的方法可以做到这一点？显然，我想包括酒店、汽车和火车部分，因为它们是重要的信息。

伊扎克·哈宾斯基

这是使用 XQuery 和 FLWOR 表达式的解决方案。所有航段（F：Flight）通过UNION ALL. 最终的 XML 是通过 FLWOR 表达式直接形成的。

SQL

-- DDL and sample data population, start
DECLARE @TripHistoryTest TABLE
(
    [BarCode] nvarchar(100),
    [PassengerName] nvarchar(100),
    [SegmentType] char(1),
    [Flight1_From] nvarchar(100),
    [Flight1_FromTime] nvarchar(100),
    [Flight1_To] nvarchar(100),
    [Flight1_ToTime] nvarchar(100),
    [Flight2_From] nvarchar(100),
    [Flight2_FromTime] nvarchar(100),
    [Flight2_To] nvarchar(100),
    [Flight2_ToTime] nvarchar(100),
    [Flight3_From] nvarchar(100),
    [Flight3_FromTime] nvarchar(100),
    [Flight3_To] nvarchar(100),
    [Flight3_ToTime] nvarchar(100),
    [Flight4_From] nvarchar(100),
    [Flight4_FromTime] nvarchar(100),
    [Flight4_To] nvarchar(100),
    [Flight4_ToTime] nvarchar(100),
    [Hotel_From] nvarchar(100),
    [Hotel_FromTime] nvarchar(100),
    [Hotel_To] nvarchar(100),
    [Hotel_ToTime] nvarchar(100),
    [Train_From] nvarchar(100),
    [Train_FromTime] nvarchar(100),
    [Train_To] nvarchar(100),
    [Train_ToTime] nvarchar(100),
    [Car_From] nvarchar(100),
    [Car_FromTime] nvarchar(100),
    [Car_To] nvarchar(100),
    [Car_ToTime] nvarchar(100)
)

INSERT INTO @TripHistoryTest
(
    [BarCode],
    [PassengerName],
    [SegmentType],
    [Flight1_From],
    [Flight1_FromTime] ,
    [Flight1_To] ,
    [Flight1_ToTime] ,
    [Flight2_From],
    [Flight2_FromTime],
    [Flight2_To],
    [Flight2_ToTime],
    [Flight3_From],
    [Flight3_FromTime],
    [Flight3_To],
    [Flight3_ToTime],
    [Flight4_From],
    [Flight4_FromTime],
    [Flight4_To],
    [Flight4_ToTime],
    [Hotel_From],
    [Hotel_FromTime],
    [Hotel_To],
    [Hotel_ToTime],
    [Train_From] ,
    [Train_FromTime] ,
    [Train_To] ,
    [Train_ToTime] ,
    [Car_From],
    [Car_FromTime],
    [Car_To],
    [Car_ToTime] 
)
VALUES
('BR666999', 'KETYM PORTA', 'F', 
'MILAN', '06:00', 'NAPOLI', '07:00',
'NAPOLI', '08:00', 'BUDAPEST', '10:00',
'BUDAPEST', '11:00', 'CRAIOVA', '13:00',
'CRAIOVA', '14:00', 'ROMA', '16:00', 
'','','','',
'','','','',
'','','','')
,('BR666999', 'KETYM PORTA', 'F', 
'ROMA', '17:00', 'TORINO', '18:00',
'TORINO', '19:00', 'MADRID', '20:00',
'','','','',
'','','','', 
'','','','',
'','','','',
'','','','')
,('BR666999', 'KETYM PORTA', 'F', 
'MADRID', '21:00', 'MANILA', '23:00',
'','','','',
'','','','',
'','','','','','','','','','','','','','','','')
,('BR666999', 'KETYM PORTA', 'T', 
'', '', '', '',
'', '', '', '',
'', '', '', '',
'', '', '', '', 
'','','','',
'MANILA','23:30','CALBAYOG','23:40',
'','','','')
,('BR666999', 'KETYM PORTA', 'H', 
'', '', '', '',
'', '', '', '',
'', '', '', '',
'', '', '', '', 
'LASTHOPEHOTEL','23:50','LASTHOPEHOTEL','06:00',
'','','','',
'','','','')
,('BR666999', 'KETYM PORTA', 'C', 
'', '', '', '',
'', '', '', '',
'', '', '', '',
'', '', '', '', 
'','','','',
'','','','',
'CALBAYOG','07:00','MARIANA TRENCH','');
-- DDL and sample data population, end

-- XQuery and FLWOR
;WITH rs AS
(
    SELECT BarCode, PassengerName
        , Flight1_From AS Flight_From
        , Flight1_FromTime AS Flight_FromTime
        , Flight1_To AS Flight_To
        , Flight1_ToTime AS Flight_ToTime
    FROM @TripHistoryTest
    WHERE SegmentType = 'F' AND Flight1_From != ''
    UNION ALL
    SELECT BarCode, PassengerName
        , Flight2_From AS Flight_From
        , Flight2_FromTime AS Flight_FromTime
        , Flight2_To AS Flight_To
        , Flight2_ToTime AS Flight_ToTime
    FROM @TripHistoryTest
    WHERE SegmentType = 'F' AND Flight2_From != ''
    UNION ALL
    SELECT BarCode, PassengerName
        , Flight3_From AS Flight_From
        , Flight3_FromTime AS Flight_FromTime
        , Flight3_To AS Flight_To
        , Flight3_ToTime AS Flight_ToTime
    FROM @TripHistoryTest
    WHERE SegmentType = 'F' AND Flight3_From != ''
    UNION ALL
    SELECT BarCode, PassengerName
        , Flight4_From AS Flight_From
        , Flight4_FromTime AS Flight_FromTime
        , Flight4_To AS Flight_To
        , Flight4_ToTime AS Flight_ToTime
    FROM @TripHistoryTest
    WHERE SegmentType = 'F' AND Flight4_From <> ''
)
SELECT (SELECT * FROM rs
ORDER BY Flight_FromTime
FOR XML PATH('r'), TYPE, ROOT('root')).query('<BARCODE_List><BARCODE>
<BarCode>{data(root/r[1]/BarCode)}</BarCode>
<PassengerList>
{
    for $p in distinct-values(/root/r/PassengerName)
    return <Passenger>
            <PassengerName>{$p}</PassengerName>
        </Passenger>
}
</PassengerList>
<Segment_List>
{
    for $x in /root/r
    return <AirSeg>
            {$x/Flight_From}
            {$x/Flight_FromTime}
            {$x/Flight_To}
            {$x/Flight_ToTime}
        </AirSeg>
}
</Segment_List>
</BARCODE></BARCODE_List>');

输出

<BARCODE_List>
  <BARCODE>
    <BarCode>BR666999</BarCode>
    <PassengerList>
      <Passenger>
        <PassengerName>KETYM PORTA</PassengerName>
      </Passenger>
    </PassengerList>
    <Segment_List>
      <AirSeg>
        <Flight_From>MILAN</Flight_From>
        <Flight_FromTime>06:00</Flight_FromTime>
        <Flight_To>NAPOLI</Flight_To>
        <Flight_ToTime>07:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>NAPOLI</Flight_From>
        <Flight_FromTime>08:00</Flight_FromTime>
        <Flight_To>BUDAPEST</Flight_To>
        <Flight_ToTime>10:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>BUDAPEST</Flight_From>
        <Flight_FromTime>11:00</Flight_FromTime>
        <Flight_To>CRAIOVA</Flight_To>
        <Flight_ToTime>13:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>CRAIOVA</Flight_From>
        <Flight_FromTime>14:00</Flight_FromTime>
        <Flight_To>ROMA</Flight_To>
        <Flight_ToTime>16:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>ROMA</Flight_From>
        <Flight_FromTime>17:00</Flight_FromTime>
        <Flight_To>TORINO</Flight_To>
        <Flight_ToTime>18:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>TORINO</Flight_From>
        <Flight_FromTime>19:00</Flight_FromTime>
        <Flight_To>MADRID</Flight_To>
        <Flight_ToTime>20:00</Flight_ToTime>
      </AirSeg>
      <AirSeg>
        <Flight_From>MADRID</Flight_From>
        <Flight_FromTime>21:00</Flight_FromTime>
        <Flight_To>MANILA</Flight_To>
        <Flight_ToTime>23:00</Flight_ToTime>
      </AirSeg>
    </Segment_List>
  </BARCODE>
</BARCODE_List>

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

从 XML 节点中提取数据

从XML文件中提取表数据

在Ansible中提取并合并json节点

从Postgresql上的表中提取R中的XML数据

如何从 XML 中提取数据

从XML标签中提取数据

从xml文件中提取数据？

如何以xml形式提取节点js中的表数据？

从多个 XML 节点中提取值

从 xml 节点集中提取文本

从特定的 xml 节点中提取值

从xml节点中提取值

从XML文件中提取特定节点

读取R中的多个xml文件并合并数据

使用正文路径表达式从 WCF-SQL 消息中提取 XML 节点中的 XML 文档

从样式表中提取 xml 属性

从 XML 节点中提取多个值并将其映射到数据库

从 xml 中提取所有父节点和子节点

从Oracle中重复的节点中提取特定的xml节点

无法使用路径提取器从响应中提取 xml 值

在R中提取并合并

从XML文件中提取参数数据

删除从 XML 中提取的重复数据

在VBA中从XML响应中提取数据

从嵌套的XML PostgreSQL中提取数据

从ajax结果中提取XML数据

如何从xml中提取所需的数据

从 .xml 文件中提取熊猫数据框

使用SimpleXml从xml文件中提取数据

TOP 榜单

  1. 1

    UITableView的项目向下滚动后更改颜色，然后快速备份

  2. 2

    Linux的官方Adobe Flash存储库是否已过时？

  3. 3

    用日期数据透视表和日期顺序查询

  4. 4

    应用发明者仅从列表中选择一个随机项一次

  5. 5

    Mac OS X更新后的GRUB 2问题

  6. 6

    验证REST API参数

  7. 7

    Java Eclipse中的错误13，如何解决？

  8. 8

    带有错误“ where”条件的查询如何返回结果？

  9. 9

    ggplot：对齐多个分面图-所有大小不同的分面

  10. 10

    尝试反复更改屏幕上按钮的位置 - kotlin android studio

  11. 11

    如何从视图一次更新多行（ASP.NET - Core）

  12. 12

    计算数据帧中每行的NA

  13. 13

    蓝屏死机没有修复解决方案

  14. 14

    在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

  15. 15

    离子动态工具栏背景色

  16. 16

    VB.net将2条特定行导出到DataGridView

  17. 17

    通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

  18. 18

    在Windows 7中无法删除文件（2）

  19. 19

    python中的boto3文件上传

  20. 20

    当我尝试下载 StanfordNLP en 模型时，出现错误

  21. 21

    Node.js中未捕获的异常错误，发生调用

热门标签

归档