Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    5

    Unanswered: Converting DBCLOB/CLOB to XML

    Hi,
    We are facing problem in converting/casting DBCLOB to XML. The details
    of the same below
    Background:
    We are storing some xml data in a column of type DBCLOB (1073741823).
    For one of our requirement, we have to convert this data to XML type so
    that we can take advantage of Xquery to filter the result. For doing
    this conversion, we are using the following SQL query
    SELECT XMLCAST (XMLPARSE (DOCUMENT (CAST (CAST (COLUMN1 AS DBCLOB
    (32672)) AS VARCHAR (32672)))) AS XML from TABLE1
    to convert DBCLOB to XML data type.
    Problem
    For some scenario the size of data in DBCLOB column is more than 32672
    and, since we are converting DBCLOB to XML via VARCHAR, so the output
    get limited to 32672, and XML conversion fails.

    Can you please help us in this.

    Thanks in advance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why CAST to VARCHAR and outer XMLCAST?

    XMLPARSE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Last edited by tonkuma; 07-05-12 at 10:19. Reason: Add XMLCAST

  3. #3
    Join Date
    Aug 2009
    Posts
    5
    Thanks tonkuma for your reply. Actually i was casting it to varchar as XMLPARSE function was expecting a string expression.

    After going through the documentation again, i converted it to blob and then to XML. It worked, the sample query which worked is given below for reference.

    SELECT XMLCAST (XMLPARSE (DOCUMENT CAST (COLUMN1 AS BLOB) PRESERVE WHITESPACE) as XML) from TABLE1

    Thanks a lot again

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The XMLPARSE function returns an XML value.
    So, outer XMLCAST might be unnecessary.

  5. #5
    Join Date
    Aug 2009
    Posts
    5
    Correct....absolutely...outer XMLCast is redundant....thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •