This discussion is archived
6 Replies Latest reply: Aug 9, 2012 8:27 AM by 928536 RSS

Query XML (CLOB) datatype

928536 Newbie
Currently Being Moderated
Hello,

I have been trying this for a few days now and no luck ....

I am trying the query a table that has a CLOB column with XML data called "content"

the XML is ...

<?xml version="1.0" encoding="UTF-8"?>
<p:JMSTextBody xsi:type="p:JMSTextBody" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://com.ibm.websphere.jms.data.bindings/schema">
<p:value>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?>&lt;PositiveAcknowledge>&lt;MessageId>AC007228247164628 |UpdateOrder|20120717007228241344056876641&lt;/MessageId>&lt;MessageDate>08042012&lt;/MessageDate>&lt;MessageTime>0007&lt;/MessageTime>&lt;OrderNumber>AC007228247164628&lt;/OrderNumber>&lt;HostAckErrorStatus>00&lt;/HostAckErrorStatus>&lt;MtsHostOrderNumber>2046452161&lt;/MtsHostOrderNumber>&lt;MtsTn>2046452161&lt;/MtsTn>&lt;MtsQueue>D&lt;/MtsQueue>&lt;MtsEnvironment>&lt;/MtsEnvironment>&lt;MtsHostPrimaryKey>2012071700722824&lt;/MtsHostPrimaryKey>&lt;MtsSplitType>&lt;/MtsSplitType>&lt;MtsHost>AccessCare&lt;/MtsHost>&lt;MtsTrackingNumber>&lt;/MtsTrackingNumber>&lt;MtsCkt0101>&lt;/MtsCkt0101>&lt;MtsTkt>&lt;/MtsTkt>&lt;MtsTicketNumber>2012071700722824&lt;/MtsTicketNumber>&lt;/PositiveAcknowledge></p:value>
</p:JMSTextBody>

I have tried select statements with extract, extractvalue, contains ... and getting nothing

any help is appreciated...

also I am using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

Thank you in advance


I forgot to add that I'm trying to search for the item in bold or "|UpdateOrder|20120717007228241344056876641"

Edited by: Arvin on Aug 8, 2012 9:01 AM

Edited by: Arvin on Aug 8, 2012 9:05 AM

Edited by: Arvin on Aug 8, 2012 9:09 AM
  • 1. Re: Query XML (CLOB) datatype
    odie_63 Guru
    Currently Being Moderated
    Hi,

    There are two things to be aware of in this case :

    - You have to declare namespace mappings

    - The main XML document (whose root is <p:JMSTextBody>) actually embeds an XML payload in its escaped form : the one starting with <PositiveAcknowledge>.
    For the parser, this second XML appears as a regular text() node, nothing more.

    To resolve this situation, you have to parse in two passes :

    1) Extracting the embedded doc as a string
    2) Converting the string to XML and extract required data

    Here's an example assuming "MY_TABLE" as your table name :
    SQL> select x2.*
      2  from my_table t
      3     , xmltable(
      4         xmlnamespaces(default 'http://com.ibm.websphere.jms.data.bindings/schema')
      5       , '/JMSTextBody'
      6         passing xmltype(t.content)
      7         columns xml_content clob path 'value'
      8       ) x1
      9     , xmltable(
     10         '/PositiveAcknowledge'
     11         passing xmltype(x1.xml_content)
     12         columns MessageId     varchar2(80) path 'MessageId'
     13                   , MessageDate   varchar2(8)  path 'MessageDate'
     14                   , MessageTime   varchar2(4)  path 'MessageTime'
     15                   , OrderNumber   varchar2(20) path 'OrderNumber'
     16       ) x2
     17  ;
     
    MESSAGEID                                                                        MESSAGEDATE MESSAGETIME ORDERNUMBER
    -------------------------------------------------------------------------------- ----------- ----------- --------------------
    AC007228247164628  |UpdateOrder|20120717007228241344056876641                    08042012    0007        AC007228247164628
     
    Note : given your db version, this will work for embedded document no more than 4000 chars in size.
    Although I used CLOB datatype to project the value (in X1), full CLOB support was only added in version 11.2.0.2.

    Here's a similar thread and solution if the XML payload exceeds 4000 characters : {thread:id=2417288}

    Edited by: odie_63 on 8 août 2012 18:21
  • 2. Re: Query XML (CLOB) datatype
    928536 Newbie
    Currently Being Moderated
    Wow !

    thank you Odie_63 for the fast reply ....

    I have a better understanding .... I like the way you explained it

    I tested the now I get the following errors ...

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.XMLTYPE", line 254
    ORA-06512: at line 1

    also if i wanted to .add a "where" or "like" clause ... do I just add that after the "x2" line 16


    Thank you in advance

    Arv
  • 3. Re: Query XML (CLOB) datatype
    odie_63 Guru
    Currently Being Moderated
    I tested the now I get the following errors ...

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.XMLTYPE", line 254
    ORA-06512: at line 1
    Are you testing on the same XML you posted or on a broader data set?
    You may be hitting the limitation I mentioned about the size.

    also if i wanted to .add a "where" or "like" clause ... do I just add that after the "x2" line 16
    Yes, just like a regular query, there's nothing different.
  • 4. Re: Query XML (CLOB) datatype
    928536 Newbie
    Currently Being Moderated
    odie_63 wrote:
    I tested the now I get the following errors ...

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.XMLTYPE", line 254
    ORA-06512: at line 1
    Are you testing on the same XML you posted or on a broader data set?
    You may be hitting the limitation I mentioned about the size.

    also if i wanted to .add a "where" or "like" clause ... do I just add that after the "x2" line 16
    Yes, just like a regular query, there's nothing different.
    Yes, I'm searching on a broader data set .... is this where I would set the buffer? (so I have read)  : ) ....

    Sorry ...I meant to say that I am searching the same table ...


    and thank you for answering the "where" clause question : )

    Sorry for all of these question ...i'm completely new to this so this also i really good learning experience for me

    Edited by: Arvin on Aug 8, 2012 10:25 AM
  • 5. Re: Query XML (CLOB) datatype
    odie_63 Guru
    Currently Being Moderated
    Sorry ...I meant to say that I am searching the same table ...
    So could you check if some contents are > 4k?

    If you find any then you must use the solution described in the thread I mentioned.
  • 6. Re: Query XML (CLOB) datatype
    928536 Newbie
    Currently Being Moderated
    Hello,

    okay ... so i've been playing around a bit and feel like we're getting somewhere : )

    Yes, you were correct with your assumption that we have some content over the 4k limit, however the work around that I came up with is to simply filter out records that were over 4k and the query works : )

    Here is the revised query .....

    select x2.*
    from MTS_WASCONN.TRANSACTIONAUDIT t
    , xmltable(
    xmlnamespaces(default 'http://com.ibm.websphere.jms.data.bindings/schema')
    , '/JMSTextBody'
    passing xmltype(t.content)
    columns xml_content clob path 'value'
    ) x1, xmltable
    ('/PositiveAcknowledge'
    passing xmltype(x1.xml_content)
    columns MessageId varchar2(80) path 'MessageId'
    , MessageDate varchar2(8) path 'MessageDate'
    , MessageTime varchar2(4) path 'MessageTime'
    , OrderNumber varchar2(20) path 'OrderNumber'
    , HostAckErrorStatus varchar2(8) path 'HostAckErrorStatus'
    , MtsHostOrderNumber varchar2(10) path 'MtsHostOrderNumber'
    , MtsTn varchar2(10) path 'MtsTn'
    , MtsQueue varchar2(4) path 'MtsQueue'
    , MtsEnvironment varchar2(2) path 'MtsEnvironment'
    , MtsHostPrimaryKey varchar2(20) path 'MtsHostPrimaryKey'
    , MtsSplitType varchar2(4) path 'MtsSplitType'
    , MtsHost varchar2(10) path 'MtsHost'
    , MtsTrackingNumber varchar2(20) path 'MtsTrackingNumber'
    , MtsCkt0101 varchar2(10) path 'MtsCkt0101'
    , MtsTkt varchar2(20) path 'MtsTkt'
    , MtsTicketNumber varchar2(20) path 'MtsTicketNumber'
    ) x2 where ServiceName NOT LIKE 'Dispatch'
    ;


    Now ...lol ... I'm trying to add ") x2 where ServiceName NOT LIKE 'Dispatch' and MESSAGEID like '2012080300761031%' to the end of it and started to get ORA-31167: XML nodes over 64K in size cannot be inserted

Legend

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