Your query doesn't work with your example XML, because the
DebitAccount
node is not a direct child of the
Request
node.
Change your query to specify the correct path to the node:
select CAST(CAST(REQ.MQXMLdata AS XML).query('data(/*/Request/SettlementPostingRequest/DebitAccount/DebitAccountNo)') AS VARCHAR(50)) AS DebitAccountNumber from test