/*Add the vendor's order ID here - Must be 10 characters*/
declare @VendorOrderID char(10) = '0690604978';
declare /*Date range to look into*/
@FromDate datetime = dateadd(month, -1, getdate())
, @ToDate datetime = getdate()
;
declare @PayloadID uniqueidentifier;
;with xmlnamespaces(default 'http://redacted')
select
@PayloadID = cam1.cam_groupid
from CustomerAppMessages (nolock) as cam1
cross apply cam1.cam_xml.nodes('OrderAcknowledgementResponseMsg') as request(ack)
where
cam1.cam_inserted between @FromDate and @ToDate