ibm-mqmessagebrokeribm-integration-busextended-sql

How to convert hexBinary (MsgId) to character in esql?

I'm trying to put MQMD.MsgId to XMLNSC.MsgId field, like this:

SET OutputRoot.XMLNSC.Root.MsgId = InputRoot.MQMD.MsgId;

But I'm getting X'414d51204d39392e5352442e4330302e56c47bd4203b3708' instead of just 414d51204d39392e5352442e4330302e56c47bd4203b3708.

Also i've tried to cast MsgId to CHARACTER, but result is the same.

How to get rid of quotes and 'X'?

Solution

  • You could try something like this:

    DECLARE msgId CHARACTER CAST(InputRoot.MQMD.MsgId AS CHARACTER);
SET OutputRoot.XMLNSC.Root.MsgId = SUBSTRING(msgId FROM 3 FOR LENGTH(msgId) - 3);