Select values in SQL Server from XML

Simple nodes and value functions should be sufficient:

DECLARE @xml XML = N'<SearchResponse>
    <Data>
        <Information>
            <Code>Code 1</Code>
            <Options>
                <Option>
                    <TID>1</TID>
                    <Rooms>
                        <ReturnRoom>
                            <RoomId>1</RoomId>
                            <Description>Single</Description>
                        </ReturnRoom>
                        <ReturnRoom>
                            <RoomId>2</RoomId>
                            <Description>Standard</Description>
                        </ReturnRoom>
                    </Rooms>
                </Option>
                <Option>
                    <TID>2</TID>
                    <Rooms>
                        <ReturnRoom>
                            <RoomId>1</RoomId>
                            <Description>Single</Description>
                        </ReturnRoom>
                    </Rooms>
                </Option>
            </Options>
        </Information>      
    </Data>
</SearchResponse>';


SELECT  b.value('(../../../../Code/text())[1]', 'NVARCHAR(MAX)') AS Code
       ,b.value('(../../TID/text())[1]', 'NVARCHAR(MAX)') AS TID
       ,b.value('(./RoomId/text())[1]', 'NVARCHAR(MAX)') AS RoomId
       ,b.value('(./Description/text())[1]', 'NVARCHAR(MAX)') AS Description
FROM @xml.nodes('/SearchResponse/Data/Information/Options/Option/Rooms/ReturnRoom') AS a(b);

Rextester demo

EDIT:

If you really need to aggregate string you could use:

WITH cte AS (
  SELECT  b.value('(../../../../Code/text())[1]', 'NVARCHAR(MAX)') AS Code
       ,b.value('(../../TID/text())[1]', 'NVARCHAR(MAX)') AS TID
       ,b.value('(./RoomId/text())[1]', 'NVARCHAR(MAX)') AS RoomId
       ,b.value('(./Description/text())[1]', 'NVARCHAR(MAX)') AS Description
  FROM @xml.nodes('/SearchResponse/Data/Information/Options/Option/Rooms/ReturnRoom') AS a(b)
)
SELECT Code, TID,
      STRING_AGG(RoomId, ';') AS RoomId,
      STRING_AGG(Description, ';') AS Description
FROM cte
GROUP BY Code, TID;

This will work for SQL Server 2017+. With lower version you have to use different method like XML or CLR function.

DBFiddle

EDIT 2:

As @Shnugo avoid multiple backward navigation could be avoided by using CROSS APPLY:

WITH cte AS (
  SELECT  b.value('(../../Code/text())[1]', 'NVARCHAR(MAX)') AS Code
       ,b.value('(TID/text())[1]', 'NVARCHAR(MAX)') AS TID
       ,d.value('(RoomId/text())[1]', 'NVARCHAR(MAX)') AS RoomId
       ,d.value('(Description/text())[1]', 'NVARCHAR(MAX)') AS Description
  FROM @xml.nodes('/SearchResponse/Data/Information/Options/Option') AS a(b)
  CROSS APPLY a.b.nodes('Rooms/ReturnRoom') AS c(d)
)
SELECT Code, TID, STRING_AGG (RoomId, ';'), STRING_AGG(Description, ';')
FROM cte
GROUP BY Code, TID;

DBFiddle 2