You're overcomplicating things.  You're using two separate methods to find the MAX() date.  You only need one.

SELECT
    [TSI].[ZATS_BROKER_FEED].[HTS_NUMBER]
,    #TEMP1.Part_Num
,    1 -- This will always be...

You're asking for advice from the Internet on what records to delete from your database?

Only you can say whether the query is right, but you should ALWAYS TEST...

OFFSET/FETCH was introduced in SQL 2012 specifically to handle paging, which is what you are trying to do.

Drew

SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;

This only requires 1 scan,...

Since you're only looking for ONE row, there is no reason to use the relatively expensive .nodes() function.

SELECT @XML.value('(/tokens/token/typeid/text())[1]', 'int') AS typeidid,
    @XML.value('(/tokens/token/p1/text())[1]', 'decimal(6,3)') AS p1id