6 Replies Latest reply: Aug 9, 2012 10:27 AM by 928536 RSS

    Query XML (CLOB) datatype

    928536
      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
          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
            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
              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
                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
                  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
                    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