SQL Server: How to join two xml element by xquery

Question

How can i join two xml element by xquery. this is my xml and some one from this forum show me how to achieve the output without joining.

declare @xml as xml = N'<TickerBrokerDateMap>
  <Broker ID="SB" Ticker_Id="EQNR" BrokerCategory="Contributing" Client="">
  <Broker_Id>1</Broker_Id>
  </Broker>
  <Broker ID="4F-C1" Ticker_Id="EQNR" BrokerCategory="Contributing" Client="">
  <Broker_Id>2</Broker_Id>
  </Broker>
  <TickerBrokerCoordinateMap BrokerTab_Id="0" Broker_Id="0" Ticker_Id="EQNR">
  <Identity>0</Identity>
  <ID>0</ID>
  <PeriodFormat>2</PeriodFormat>
  </TickerBrokerCoordinateMap>
  <TickerBrokerCoordinateMap BrokerTab_Id="1" Broker_Id="1" Ticker_Id="EQNR">
  <Identity>1</Identity>
  <ID>0</ID>
  <PeriodFormat>3</PeriodFormat>
  </TickerBrokerCoordinateMap>
 </TickerBrokerDateMap>'

Now extracting data from above xml this way.

declare @list as varchar(max) = 'SB,4F-C1'

 select ticker.n.query('.')
 from @xml.nodes('/*/TickerBrokerCoordinateMap[PeriodFormat=3]') as ticker(n)
 cross apply @xml.nodes('/*/Broker') as broker(n)
 cross apply string_split(@list,',') as list
 where broker.n.value('@ID', 'varchar(max)') = ltrim(list.value)
 and ticker.n.value('@Broker_Id', 'int') = broker.n.value('Broker_Id[1]', 'int')

How can i achieve the same joining two element called Broker and TickerBrokerCoordinateMap based on Broker_Id and in where clause i will mention PeriodFormat==3 and ID in ('SB','4F-C1')

if the same output can be develop by join then please guide me. thanks

Answer 1

     declare @list as varchar(max) = 'SB,4F-C1'  
              
      select ticker.n.query('.')  
      from @xml.nodes('/TickerBrokerDateMap/TickerBrokerCoordinateMap[PeriodFormat=3]') as ticker(n)  
      join @xml.nodes('/TickerBrokerDateMap/Broker') as broker(n) on    
           ticker.n.value('@Broker_Id', 'int') = broker.n.value('Broker_Id[1]', 'int')  
      join string_split(@list,',') as list on broker.n.value('@ID', 'varchar(20)') = ltrim(list.value)