Is there any DB2 solution which transforms a BLOB (> 32 K) to a CLOB?

Question

I MUST store XMLs (size > 32K) in a BLOB field in a DB2 database.

In an external source I need a CLOB, which preserves the XML structure.

WHEN I run this then I get only the text content:

SELECT 
  CAST(
  XMLCAST (
    XMLPARSE (
      DOCUMENT CAST (
        PAYLOAD AS BLOB
      ) 
      PRESERVE WHITESPACE
    ) as XML
  ) as CLOB )
FROM 
U.Content;

Is there any DB2 solution which transforms a BLOB (> 32 K) to a CLOB?

Answer 1

Use XMLSERIALIZE instead of XMLCAST:

SELECT
  LENGTH (B) BLOB_LEN
, LENGTH 
  (
    -- BLOB -> XML -> CLOB
    XMLSERIALIZE (XMLPARSE (DOCUMENT B) AS CLOB (100K))
  ) XML_LEN
FROM 
(
  VALUES 
  -- ~100K BLOB from XML doc
  XMLSERIALIZE (XMLELEMENT (NAME "DOC", REPEAT (CLOB ('A'), 100000)) AS BLOB (100K))
) T (B)

BLOB_LEN	XML_LEN
100011	100011

Fiddle