Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    4

    Red face Unanswered: CLOB column in Oracle 10.2 DB

    I have an Oracle Table with a CLOB column that has XML stored in the CLOB column. I need to be able to query the individual properties in the xml to build columns for a report. Can this be done in a select statement? Do I have to use PL/SQL? if so, does anyone have an example?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Simple:
    Code:
    SELECT XMLTYPE(x.myclob_xml).EXTRACT('/some/path/text()').GETSTRINGVAL()
      FROM table_with_xml_clob x;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2013
    Posts
    4

    CLOB column in Oracle 10.2 DB

    SELECT XMLTYPE(x.myclob_xml).EXTRACT('/some/path/text()').GETSTRINGVAL()
    FROM table_with_xml_clob x;

    What value do I put in /some/path/text() ?

    Thanks

    Dave

  4. #4
    Join Date
    Sep 2013
    Posts
    4
    This is the SQL I am trying:

    SELECT XMLTYPE(x.details).EXTRACT('/DocumentEditRequest/TargetDocuments/Properties/text()').GETSTRINGVAL()
    FROM SEDSE.T_SEDS_AUDIT x
    where event_type_id = -11;

    The CLOB column with XML data is details, the table is SEDSE.T_SEDS_AUDIT

    Only records with event_type_id = -11 have the xml data

    The path I used is in the xml data, TargetDocuments/Properties

    I assume text is used to get the text value

  5. #5
    Join Date
    Sep 2013
    Posts
    4
    I get rows returned but no values

Tags for this Thread

Posting Permissions

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