–simple xquery example utilizing .nodes() with a CROSS APPLY
SELECT
X.value(‘OrderId[1]’, ‘VARCHAR(25)’) As [orderid],
X.value(‘Status[1]’, ‘VARCHAR(10)’) As [status]
FROM YourTable
CROSS APPLY xmlField.nodes(‘head/body/response/orders/order’) AS T(X)
WHERE
X.value(‘Status[1]’, ‘VARCHAR(10)’) =‘O’