This discussion is archived
5 Replies Latest reply: Jul 25, 2012 1:39 AM by odie_63 RSS

Newbie trying to query an XML field in Clob

933972 Newbie
Currently Being Moderated
I've been unsuccessfully trying to query XML data in a CLOB field today without much success.

The clob field stores approvals (Edited, Reviewed, Published) for each version.
I'm trying to get the latest version (in this case 13) and what state (name) it is at (in this case 'Reviewed')

Below is an example of the table and field with a single XML column...
desc WIKI.OS_PROPERTYENTRY

 Name                                              Null?       Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ENTITY_NAME                                         NOT NULL VARCHAR2(125)
 ENTITY_ID                                         NOT NULL NUMBER(19)
 ENTITY_KEY                                         NOT NULL VARCHAR2(200)
 KEY_TYPE                                               NUMBER(10)
 BOOLEAN_VAL                                               NUMBER(1)
 DOUBLE_VAL                                               FLOAT(126)
 STRING_VAL                                               VARCHAR2(255)
 TEXT_VAL                                               CLOB
 LONG_VAL                                               NUMBER(19)
 INT_VAL                                               NUMBER(10)
 DATE_VAL                                               DATE

SQL> select xmltype(text_val) from wiki.os_propertyentry where entity_id = 7274716 and entity_key = 'com.comalatech.workflow.approvals';

XMLTYPE(TEXT_VAL)
----------------------------------------------------------------------------------------------------------------------------------
<ApprovalChecks>
  <ApprovalCheck>
    <name>Edited</name>
    <version>7</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2008-10-31 13:47:25.638 NZDT</date>
     <comment>Initial import from Word document without change</comment>
      </Approver>
    </approvers>
    <weight>10</weight>
    <id>1</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Reviewed</name>
    <version>7</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2008-10-31 13:47:30.532 NZDT</date>
     <comment/>
      </Approver>
    </approvers>
    <weight>20</weight>
    <id>2</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Signoff</name>
    <version>7</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2008-10-31 13:47:32.532 NZDT</date>
     <comment/>
      </Approver>
    </approvers>
    <weight>30</weight>
    <id>3</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Edited</name>
    <version>8</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2008-11-02 10:54:07.903 NZDT</date>
     <comment>Updated references re Wiki, removed unwanted sections</comment>
      </Approver>
    </approvers>
    <weight>10</weight>
    <id>4</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Reviewed</name>
    <version>8</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2008-11-02 10:54:10.552 NZDT</date>
     <comment/>
      </Approver>
    </approvers>
    <weight>20</weight>
    <id>5</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Signoff</name>
    <version>8</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2008-11-02 10:54:13.161 NZDT</date>
     <comment/>
      </Approver>
    </approvers>
    <weight>30</weight>
    <id>6</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Edited</name>
    <version>9</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2009-11-13 08:53:00.649 NZDT</date>
     <comment>Testing approval information</comment>
      </Approver>
    </approvers>
    <weight>10</weight>
    <id>7</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Edited</name>
    <version>9</version>
    <approvers>
      <Approver>
     <approved>false</approved>
     <user>bgra030</user>
     <date>2009-11-13 08:53:13.830 NZDT</date>
     <comment/>
      </Approver>
    </approvers>
    <weight>10</weight>
    <id>8</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Edited</name>
    <version>12</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2009-11-23 11:08:14.666 NZDT</date>
     <comment>Changes due to self auditing, using Study Audit Checklist and DM Study Checklist</comment>
      </Approver>
    </approvers>
    <weight>10</weight>
    <id>9</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Reviewed</name>
    <version>12</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2009-11-23 11:08:20.345 NZDT</date>
     <comment/>
      </Approver>
    </approvers>
    <weight>20</weight>
    <id>10</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Signoff</name>
    <version>12</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2009-11-23 11:08:23.997 NZDT</date>
     <comment/>
      </Approver>
    </approvers>
    <weight>30</weight>
    <id>11</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Edited</name>
    <version>13</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2011-10-31 10:42:37.703 NZDT</date>
     <comment>No change, just change of name of a linked page</comment>
      </Approver>
    </approvers>
    <weight>10</weight>
    <id>12</id>
    <stateId>0</stateId>
    <attachments/>
  </ApprovalCheck>
  <ApprovalCheck>
    <name>Reviewed</name>
    <version>13</version>
    <approvers>
      <Approver>
     <approved>true</approved>
     <user>bgra030</user>
     <date>2011-10-31 10:42:40.596 NZDT</date>
     <comm


1 row selected.
My query is way off,... I can't even get it to run, let alone getting the record I want without some hard coding...

Can anyone help?

Thanks in advance.
select y.y_name, y.y_version, z.z_approved, z.z_user, z.z_date, z.z_comment
from WIKI.os_propertyentry t,
     XMLTABLE('$p/ApprovalChecks/ApprovalCheck'
              PASSING t.text_val as "p"
              COLUMNS y_name       VARCHAR2(30) PATH 'ApprovalCheck/name',
                      y_version    NUMBER       PATH 'ApprovalCheck/version',
                      Approver     XMLTYPE      PATH 'ApprovalCheck/approvers/Approver/*') y,
     XMLTABLE('$a/Approver'
              PASSING y.Approver as "a"
              COLUMNS z_approved  VARCHAR2(8)   PATH 'Approver/approved',
                      z_user      VARCHAR2(30)  PATH 'Approver/user',
                      z_date      DATE          PATH 'Approver/date',
                      z_comment   VARCHAR2(100) PATH 'Approver/comment') z
where entity_id = 7274716 and entity_key = 'com.comalatech.workflow.approvals'
and   t.name = 'Reviewed'
and   t.version = 13;


           PASSING t.text_val as "p"
                      *
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected - got CLOB
  • 1. Re: Newbie trying to query an XML field in Clob
    AlexAnd Guru
    Currently Being Moderated
    try
    select y.y_name, y.y_version, z.z_approved, z.z_user, z.z_date, z.z_comment
    from WIKI.os_propertyentry t,
         XMLTABLE('$p/ApprovalChecks/ApprovalCheck'
                  PASSING xmltype(t.text_val) as "p"                    --this
                  COLUMNS y_name       VARCHAR2(30) PATH 'name',  --this
                          y_version    NUMBER       PATH 'version', --this
                          Approver     XMLTYPE      PATH 'approvers/Approver/*') y, --this
         XMLTABLE('$a' --this
                  PASSING y.Approver as "a"
                  COLUMNS z_approved  VARCHAR2(8)   PATH '/approved', --this
                          z_user      VARCHAR2(30)  PATH '/user', --this
                          z_date      /*DATE*/ VARCHAR2(30)          PATH '/date', --this
                          z_comment   VARCHAR2(100) PATH '/comment') z --this
    where entity_id = 7274716 and entity_key = 'com.comalatech.workflow.approvals'
    and   t.name = 'Reviewed'
    and   t.version = 13;
    z_date isn't date. it's looks like timestamp with timezonew
  • 2. Re: Newbie trying to query an XML field in Clob
    odie_63 Guru
    Currently Being Moderated
    Hi,

    In addition to Alex's reply.

    If you don't expect multiple Approver under a given ApproverCheck then you don't have to use a second XMLTable.

    How are you defining the "latest" version? Is it the one whose date is the latest, or the record that appears last in document order? (in your example both options are true).

    The usual approach is a Top-N query :
    SQL> with all_versions as (
      2    select y_name
      3         , y_version
      4         , z_approved
      5         , z_user
      6         , z_comment
      7         , row_number()
      8             over( order by to_timestamp_tz(z_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZD') desc ) as rn
      9    from os_propertyentry t,
     10         xmltable(
     11           '/ApprovalChecks/ApprovalCheck'
     12           passing xmltype(t.text_val)
     13           columns y_name       varchar2(30)  path 'name'
     14                 , y_version    number        path 'version'
     15                 , z_approved   varchar2(8)   path 'approvers/Approver/approved'
     16                 , z_user       varchar2(30)  path 'approvers/Approver/user'
     17                 , z_date       varchar2(30)  path 'approvers/Approver/date'
     18                 , z_comment    varchar2(100) path 'approvers/Approver/comment'
     19         ) x
     20  )
     21  select y_name, y_version
     22  from all_versions
     23  where rn = 1
     24  ;
     
    Y_NAME                          Y_VERSION
    ------------------------------ ----------
    Reviewed                               13
     
  • 3. Re: Newbie trying to query an XML field in Clob
    933972 Newbie
    Currently Being Moderated
    Apologies for the late reply.

    Thanks. That is very helpful. The latest version is defined by the record in the last document order which would be the latest date (as you mentioned).
  • 4. Re: Newbie trying to query an XML field in Clob
    933972 Newbie
    Currently Being Moderated
    Further to the above, the query returns a single record okay,...
    SQL > l   
      1  with all_versions as (
      2    select c.title, y.y_name, y.y_version, z.z_approved, z.z_user, z.z_date z_date, z.z_comment,
      3           row_number() over( order by to_timestamp_tz(z_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZD') desc ) as rn
      4    from wiki.os_propertyentry t, wiki.content c,
      5         XMLTABLE('$p/ApprovalChecks/ApprovalCheck'
      6               PASSING xmltype(t.text_val) as "p"
      7               COLUMNS y_name       VARCHAR2(30) PATH 'name',
      8                    y_version       NUMBER       PATH 'version',
      9                    Approver       XMLTYPE      PATH 'approvers/Approver/*') y,
     10         XMLTABLE('$a'
     11               PASSING y.Approver as "a"
     12               COLUMNS z_approved  VARCHAR2(8)   PATH '/approved',
     13                    z_user      VARCHAR2(30)  PATH '/user',
     14                    z_date      VARCHAR2(30)  PATH '/date',
     15                    z_comment      VARCHAR2(100) PATH '/comment') z
     16    where c.contentid = t.entity_id
     17    and   c.spaceid = (select spaceid from wiki.spaces where spacename = 'Quality')
     18    and   entity_id = 7274716
     19    and entity_key = 'com.comalatech.workflow.approvals'
     20    )
     21  select title, y_name, y_version
     22  from all_versions
     23* where rn = 1
    SQL> /
    
    TITLE                           Y_NAME                          Y_VERSION
    ------------------------------ ------------------------------ ----------
    Quality Audits (NIHI-5001)     Signoff                     17
    
    1 row selected.
    ... but I want to now query on all records, not just one.

    When I comment out the clause (and entity_id = xxxxx) I now get an error. I should have got ~300 records,.... what do I need to do to make this work?
    SQL > 18
     18*   and   entity_id = 7274716
    SQL> > c /and/-- and
     18*   -- and     entity_id = 7274716
    SQL > l
      1  with all_versions as (
      2    select t.entity_id, c.title, y.y_name, y.y_version, z.z_approved, z.z_user, z.z_date z_date, z.z_comment,
      3           row_number() over( order by to_timestamp_tz(z_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZD') desc ) as rn
      4    from wiki.os_propertyentry t, wiki.content c,
      5         XMLTABLE('$p/ApprovalChecks/ApprovalCheck'
      6               PASSING xmltype(t.text_val) as "p"
      7               COLUMNS y_name       VARCHAR2(30) PATH 'name',
      8                    y_version       NUMBER       PATH 'version',
      9                    Approver       XMLTYPE      PATH 'approvers/Approver/*') y,
     10         XMLTABLE('$a'
     11               PASSING y.Approver as "a"
     12               COLUMNS z_approved  VARCHAR2(8)   PATH '/approved',
     13                    z_user      VARCHAR2(30)  PATH '/user',
     14                    z_date      VARCHAR2(30)  PATH '/date',
     15                    z_comment      VARCHAR2(100) PATH '/comment') z
     16    where c.contentid = t.entity_id
     17    and   c.spaceid = (select spaceid from wiki.spaces where spacename = 'Quality')
     18    -- and     entity_id = 7274716
     19    and entity_key = 'com.comalatech.workflow.approvals'
     20    )
     21  select entity_id, title, y_name, y_version
     22  from all_versions
     23* where rn = 1
    wikiprod: OPS$IT_DBA > /
              PASSING xmltype(t.text_val) as "p"
                            *
    ERROR at line 6:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00241: entity reference is not well formed
    Error at line 8
    ORA-06512: at "SYS.XMLTYPE", line 272
    ORA-06512: at line 1
  • 5. Re: Newbie trying to query an XML field in Clob
    odie_63 Guru
    Currently Being Moderated
    The error is self-explanatory I think :
    LPX-00241: entity reference is not well formed
    means you have at least one row containing at not wellformed XML document.

    Look for XMLs that contain "&amp;" characters. A valid entity reference should be like this :
    &name;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points