5 Replies Latest reply: Jul 25, 2012 3:39 AM by odie_63 RSS

    Newbie trying to query an XML field in Clob

    933972
      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
          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
            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
              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
                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
                  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;