ibm-integration-busextended-sql

How to return XML from a CLOB in ESQL

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?

Solution

  • 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.