Complex XPath in XML columns in a SQL Server table

I have a table, containing an XML column XmlMsg. This column can contain orderstatus, vehiclestatus, ....

I am interested only in orderstatus nodes, and more exactly in the following two cases:

1. The last orderpart is "Pending".
2. The last orderpart is "Pending" and the last but one is "Completed".

An example of the first one is the following (only showing the relevant part):

<orderstatus responsecode="0"
             ...
             numberoforderparts="3">
    ...
    <orderparts>
        <orderpart orderpartnumber="1"/>
        <orderpart orderpartnumber="2"/>
        <orderpart orderpartnumber="3">
            <eventtype>Pending</eventtype>
            ...
        </orderpart>
    </orderparts>
</orderstatus>

An example of the second one is the following (also only showing the relevant part):

<orderstatus responsecode="0"
             ...
             numberoforderparts="3">
    ...
    <orderparts>
        <orderpart orderpartnumber="1"/>
        <orderpart orderpartnumber="2">
            <eventtype>Completed</eventtype>
            ...
        </orderpart>
        <orderpart orderpartnumber="3">
            <eventtype>Pending</eventtype>
            ...
        </orderpart>
    </orderparts>
</orderstatus>

I am able getting the first list with this query:

SELECT *,
    XmlMsg.value('(/orderstatus/@numberoforderparts)[1]', 'INT') AS NumberOfOrderParts,
    
    -- Retrieve the last orderpart with eventtype="Pending"
    XmlMsg.query('(/orderstatus/orderparts/orderpart[@orderpartnumber = (/orderstatus/@numberoforderparts)[1] and eventtype="Pending"])[1]') AS LastOrderPart,
    
    -- Retrieve the second-to-last orderpart with eventtype="Completed"
    XmlMsg.query('(/orderstatus/orderparts/orderpart[@orderpartnumber = (/orderstatus/@numberoforderparts)[1] - 1 and eventtype="Completed"])[1]') AS PreviousOrderPart
FROM 
    [dbo].[AWIMessageLogs]
WHERE 
    Source = 'RCV_RESP' 
    AND LogDateTime >= '2024-11-26' 
    AND LogDateTime <= '2024-11-26 15:00' 
    AND XmlMsg.exist('/orderstatus') = 1
    -- Ensure that the last orderpart has eventtype="Pending"
    AND XmlMsg.query('(/orderstatus/orderparts/orderpart[@orderpartnumber = (/orderstatus/@numberoforderparts)[1] and eventtype="Pending"])[1]') IS NOT NULL

However, I have no idea how to get the second list.

Does anybody have an idea?

The last orderpart is "Pending".

orderstatus[orderparts/orderpart[last()]/eventtype='Pending']

The last orderpart is "Pending" and the last but one is "Complete".

orderstatus[orderparts/orderpart[last()]/eventtype='Pending' and
            orderparts/orderpart[last()-1]/eventtype='Completed']