How to return XML from a CLOB in ESQL

679 Views Asked by At

I'm storing an XML value in a CLOB column, called PAYLOAD, using ABITSTREAM approach:

DECLARE xmlMsg BLOB ASBITSTREAM(InputRoot.*:SOAP);
        

and then this inside the INSERT statement:

CAST(xmlMsg AS CHAR CCSID InputRoot.Properties.CodedCharSetId)

How do I later take the results from a SELECT statement and put the XML into the OutputRoot?

1

There are 1 best solutions below

0
On BEST ANSWER

The result of the CAST is a character string containing the XML document. If you want to parse the XML document into OutputRoot then you need something like this:

    CREATE LASTCHILD OF OutputRoot DOMAIN('XMLNSC') PARSE(xmlMsg);

Note that the usual requirement to supply CCSID and ENCODING does not apply if you are parsing a CHARACTER variable.

See https://www.ibm.com/docs/en/integration-bus/9.0.0?topic=statements-create-statement for all the details of the CREATE statement with the PARSE clause.