sql server shred xml with multiple nodes in one query

The following code provides some templates to read values out of any location into a flat table. Add missing columns yourself. That should be easy.

DECLARE @xml XML=
N'<Report>
 <P>
    <Data>
      <Cust custID="A" custName="B" />
    </Data>
  </P>
  <H>
    <Data1>
      <Seats>
        <Seat id="abc" value="123" />
        <Date depart="abc1" arrive="1231" />
        <Records>
          <Record recID="C" col2="D" />
        </Records>
      </Seats>
      <Seats>
        <Seat id="xyz" value="756" />
        <Date depart="asd" arrive="6781" />
        <Records>
          <Record recID="1" col2="6" />
          <Record recID="2" col2="7" />
        </Records>
      </Seats>
    </Data1>
    <Data2>
      <S id="1" value="eco" />
      <S id="2" value="bus" />
    </Data2>
    <Data3>
      <Guest id="100" value="aaa" recID="C" />
      <Guest id="101" value="bbb" recID="1" />
      <Guest id="102" value="ccc" recID="2" />
    </Data3>
  </H>
</Report>';

--the query reads meta data out of the @xml directly and nested data out of derived tables via .nodes()

 select @xml.value('(/Report/P/Data/Cust/@custID)[1]','nvarchar(max)') AS CustomerID
       ,@xml.value('(/Report/P/Data/Cust/@custName)[1]','nvarchar(max)') AS CustomerName
       ,B.Seat.value('(Seat/@id)[1]','nvarchar(max)') AS SeatId
       ,B1.Record.value('@recID','nvarchar(max)') AS SeatRecordId
       ,C.S.value('@id','int') AS S_Id
       ,D.Guest.value('@id','int') AS Guest_Id
 INTO #FlatTable
 FROM @xml.nodes('/Report/H') AS A(DataNode)
 OUTER APPLY A.DataNode.nodes('Data1/Seats') AS B(Seat)
 OUTER APPLY B.Seat.nodes('Records/Record') AS B1(Record) 
 OUTER APPLY A.DataNode.nodes('Data2/S') AS C(S) 
 OUTER APPLY A.DataNode.nodes('Data3/Guest') AS D(Guest);

 SELECT * FROM #FlatTable;

Once you've got the data in this flat table, you can use any kind of SELECT...GROUP BY to get this prepared for inserts into specific detail tables.

If this does not help you out, you must provide more information about your target structure.

UPDATE

According to your comment you want to place the guest besides the Seat, linked via @recID. This can be done by reading this value into a normal result set column and then use this value within an XQuery predicate using sql:column():

 select @xml.value('(/Report/P/Data/Cust/@custID)[1]','nvarchar(max)') AS CustomerID
       ,@xml.value('(/Report/P/Data/Cust/@custName)[1]','nvarchar(max)') AS CustomerName
       ,B.Seat.value('(Seat/@id)[1]','nvarchar(max)') AS SeatId
       ,B2.RecordId AS SeatRecordId
       ,B3.Guest.value('@id','int') AS Guest_Id
       ,C.S.value('@id','int') AS S_Id
 INTO #FlatTable
 FROM @xml.nodes('/Report/H') AS A(DataNode)
 OUTER APPLY A.DataNode.nodes('Data1/Seats') AS B(Seat)
 OUTER APPLY B.Seat.nodes('Records/Record') AS B1(Record) 
 OUTER APPLY (SELECT B1.Record.value('@recID','nvarchar(max)')) AS B2(RecordId)
 OUTER APPLY A.DataNode.nodes('Data3/Guest[@recID=sql:column("B2.RecordId")]') AS B3(Guest)
 OUTER APPLY A.DataNode.nodes('Data2/S') AS C(S);

The result for this (don't know how you want to handle <S> values)

+------------+--------------+--------+--------------+----------+------+
| CustomerID | CustomerName | SeatId | SeatRecordId | Guest_Id | S_Id |
+------------+--------------+--------+--------------+----------+------+
| A          | B            | abc    | C            | 100      | 1    |
+------------+--------------+--------+--------------+----------+------+
| A          | B            | abc    | C            | 100      | 2    |
+------------+--------------+--------+--------------+----------+------+
| A          | B            | xyz    | 1            | 101      | 1    |
+------------+--------------+--------+--------------+----------+------+
| A          | B            | xyz    | 1            | 101      | 2    |
+------------+--------------+--------+--------------+----------+------+
| A          | B            | xyz    | 2            | 102      | 1    |
+------------+--------------+--------+--------------+----------+------+
| A          | B            | xyz    | 2            | 102      | 2    |
+------------+--------------+--------+--------------+----------+------+