SQL server XML operation sample

with lan as
(SELECT progmsgid,msgTime,
MsgText.value('(/s2sMessage/s2sHeader/@messageId)[1]', 'varchar(20)') AS messageId
FROM progressivemsglan),
wan as
(
SELECT progmsgid,msgTime,
MsgText.value('(/s2sMessage/s2sHeader/@messageId)[1]', 'varchar(20)') AS messageId
FROM progressivemsgwan
)
SELECT lan.progmsgid,lan.messageid,lan.msgtime,wan.messageid,wan.msgtime
FROM lan left join wan on lan.messageid=wan.messageid
--where wan.messageid is null
order by lan.progmsgid desc