Parse CustomerMaintenanceNotifications from MessageLog

with xmlnamespaces('urn:mining.sandvik.com:CustomerManagement:GlobalDataTypes' as ns1)
select
inputmessage, cast(replace(cast(inputmessage as nvarchar(max)), 'encoding="UTF-8"', '') as XML).value('(/ns1:CustomerMaintainNotification/Customer/Locations/Location[1]/BusinessRelations/BusinessRelation[1]/ERPStatus/Code/node())[1]', 'nvarchar(255)') AS 'ERP Status'
from MessageLog
where
date > '2017-09-26 14:00:10'
--and date < '2017-09-25 14:25:00' and siteid = 'Authorization' and category='Backend' --and inputmessage like '%105372%' and outputmessage not in ('Processed message: EquipmentMaintainNotification') --order by date desc

Leave a Reply

Your email address will not be published. Required fields are marked *