with xmlnamespaces(default 'REDACTED')
select id, payload, ord_xml
,[ServerName] = ord.c.value('@server-name', 'varchar(50)')
,[DatabaseName] = ord.c.value('@database-name', 'varchar(50)')
,[OrderId] = ord.c.value('(supply-order/@id)[1]', 'int')
from @Orders
cross apply ord_xml.nodes('order-request') as ord(c)
where ord.c.value('(supply-order/@id)[1]', 'int') = 276502