Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: DB9.7 - XML data in CLOB column

    Hi ,

    In DB2 9.7, i had a scenario which expects to store XML data in a INLINE CLOB column. Now i have to select a specific XML tag from the CLOB column. From my tests it has been understood that XPath expressions can't be used on a CLOB type column.

    Some one please suggest a way to fetch the specific piece of information from the XML data inside CLOB column.


    Thanks in Advance,
    Bala

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My idea is to convert the CLOB column to XML, then apply XPath.

    The XMLPARSE function may be used to convert to XML.

    This article might be some help.
    Quote Originally Posted by kaushal View Post
    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

  3. #3
    Join Date
    Jul 2012
    Posts
    2
    Thanks for the information tonkurma.

    Would it be affecting the performance of the fetching the data, when compare to fetching the data from a XML type column.

    Is there a way to achieve the same using Xquery?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't know so much about XML and DB2 XML support.

    Would it be affecting the performance of the fetching the data, when compare to fetching the data from a XML type column.
    XML may be better. For rxample, index on XML column must be used effectivery in XPath/XQuery.

Posting Permissions

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