WITH XMLNAMESPACES (DEFAULT 'http://www.mesa.org/xml/B2MML-V0600', 'http://www.mesa.org/xml/B2MML-V0600-AllExtensions' AS Extended)
select * from (
select m.MESSAGE_ID, m.XSLT_TRANSFORMED_CONTENT.value('(//LogicalID)[1]', 'nvarchar(max)') as trans,m.XSLT_TRANSFORMED_CONTENT.value('(//ComponentID)[1]', 'nvarchar(max)') as act,m.XSLT_TRANSFORMED_CONTENT msg from [IFACE].[B2MML].TRANSMISSION m where
created_at between '2019-01-24 00:33:31' and '2019-02-14 09:33:31' and SENDER='WebService_PPDS' )a where a.act='Production Order Response' and trans='LMS2PPDS' order by MESSAGE_ID desc