13 Replies Latest reply: Aug 22, 2012 3:03 PM by Marco Gralike RSS

    Select fields from XML in CLOB column

    698705
      Hi,

      I have the following XML stored in a table as CLOB field. What i want is to select specific fields into Relational Data.
      <?xml version=1.0 encoding=UTF-8?> 
      <msgContext>
          <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
              <jms1:JMSDeliveryMode>PERSISTENT</jms1:JMSDeliveryMode>
              <jms1:JMSTimestamp>1329217943352</jms1:JMSTimestamp>
              <jms1:JMSExpiration>0</jms1:JMSExpiration>
              <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
              <jms1:JMSPriority>4</jms1:JMSPriority>
          </JMSHeaders>
          <OtherProperties>
              <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">010000000001189CC2BC2C</auditPartnerId>
              <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Auth</auditServiceId>
              <correlationTransactionId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">d0022064-1df3-43ef-be5b-93aedc96b3b2</correlationTransactionId>
              <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.346 +0100</auditReceivedDate>
              <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
              <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.351 +0100</auditForwardedDate>
              <auditMsisdn xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
              <auditEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorthLog</auditEngineId>
              <ns0:auditInfo xmlns:ns0="http://www.tibco.com/schemas/Project_gig_reporting/tib_bw_reporting/Resources/Schemas/auditInfo.xsd">
                  <ns0:dp-logpoint>response</ns0:dp-logpoint>
                  <ns0:dp-target-url/>
                  <ns0:dp-port>20565</ns0:dp-port>
                  <ns0:dp-uri>/comp_am</ns0:dp-uri>
                  <ns0:dp-size>1365</ns0:dp-size>
                  <ns0:dp-replytoengine/>
              </ns0:auditInfo>
          </OtherProperties>
      </msgContext>
      I tried to use the xml_query below with no luck :
      SELECT  x.auditPartnerId,
                   x.auditServiceId
         FROM source s
           , XMLTable(
               '/msgContext/OtherProperties'
                passing s.messagetext
                columns 
                auditPartnerId   VARCHAR2(20) PATH 'auditPartnerId'
              , auditServiceId     VARCHAR2(20) PATH 'auditServiceId'
              ) x;
      Any help appreciated
        • 1. Re: Select fields from XML in CLOB column
          user503699
          alekons wrote:
          Any help appreciated
          You will have to convert it to xmltype first. See below:
          SQL> select * from v$version ;
          
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Release 10.2.0.5.0 - Production
          PL/SQL Release 10.2.0.5.0 - Production
          CORE     10.2.0.5.0     Production
          TNS for Linux: Version 10.2.0.5.0 - Production
          NLSRTL Version 10.2.0.5.0 - Production
          
          SQL> with src as ( select to_clob('<msgContext>
          <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
          <jms1:JMSDeliveryMode>PERSISTENT</jms1:JMSDeliveryMode>
          <jms1:JMSTimestamp>1329217943352</jms1:JMSTimestamp>
          <jms1:JMSExpiration>0</jms1:JMSExpiration>
          <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
          <jms1:JMSPriority>4</jms1:JMSPriority>
          </JMSHeaders>
          <OtherProperties>
          <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">010000000001189CC2BC2C</auditPartnerId>
          <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Auth</auditServiceId>
          <correlationTransactionId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">d0022064-1df3-43ef-be5b-93aedc96b3b2</correlationTransactionId>
          <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.346 +0100</auditReceivedDate>
          <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
          <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.351 +0100</auditForwardedDate>
          <auditMsisdn xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
          <auditEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorthLog</auditEngineId>
          <ns0:auditInfo xmlns:ns0="http://www.tibco.com/schemas/Project_gig_reporting/tib_bw_reporting/Resources/Schemas/auditInfo.xsd">
          <ns0:dp-logpoint>response</ns0:dp-logpoint>
          <ns0:dp-target-url/>
          <ns0:dp-port>20565</ns0:dp-port>
          <ns0:dp-uri>/comp_am</ns0:dp-uri>
          <ns0:dp-size>1365</ns0:dp-size>
          <ns0:dp-replytoengine/>
          </ns0:auditInfo>
          </OtherProperties>
          </msgContext>') as messagetext from dual )
          SELECT x.auditPartnerId,
          x.auditServiceId
          FROM src s
          , XMLTable(
          '/msgContext/OtherProperties'
          passing s.messagetext
          columns 
          auditPartnerId VARCHAR2(20) PATH 'auditPartnerId'
          , AUDITSERVICEID VARCHAR2(20) PATH 'auditServiceId'
          ) x;
           36   37  passing s.messagetext
                  *
          ERROR at line 33:
          ORA-00932: inconsistent datatypes: expected - got CLOB
          
          
          with src as ( select xmltype(to_clob('<msgContext>
          <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
          <jms1:JMSDeliveryMode>PERSISTENT</jms1:JMSDeliveryMode>
          <jms1:JMSTimestamp>1329217943352</jms1:JMSTimestamp>
          <jms1:JMSExpiration>0</jms1:JMSExpiration>
          <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
          <jms1:JMSPriority>4</jms1:JMSPriority>
          </JMSHeaders>
          <OtherProperties>
          <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">010000000001189CC2BC2C</auditPartnerId>
          <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Auth</auditServiceId>
          <correlationTransactionId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">d0022064-1df3-43ef-be5b-93aedc96b3b2</correlationTransactionId>
          <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.346 +0100</auditReceivedDate>
          <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
          <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.351 +0100</auditForwardedDate>
          <auditMsisdn xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
          <auditEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorthLog</auditEngineId>
          <ns0:auditInfo xmlns:ns0="http://www.tibco.com/schemas/Project_gig_reporting/tib_bw_reporting/Resources/Schemas/auditInfo.xsd">
          <ns0:dp-logpoint>response</ns0:dp-logpoint>
          <ns0:dp-target-url/>
          <ns0:dp-port>20565</ns0:dp-port>
          <ns0:dp-uri>/comp_am</ns0:dp-uri>
          <ns0:dp-size>1365</ns0:dp-size>
          <ns0:dp-replytoengine/>
          </ns0:auditInfo>
          </OtherProperties>
          </msgContext>')) as messagetext from dual )
          SELECT x.auditPartnerId,
          x.auditServiceId
          FROM src s
          , XMLTable(
          '/msgContext/OtherProperties'
          passing s.messagetext
          columns 
          auditPartnerId VARCHAR2(20) PATH 'auditPartnerId'
          , AUDITSERVICEID VARCHAR2(20) PATH 'auditServiceId'
           37  ) x;
          
          AUDITPARTNERID          AUDITSERVICEID
          -------------------- --------------------
          010000000001189CC2BC Auth
          • 2. Re: Select fields from XML in CLOB column
            698705
            Tried this as per your post
            with src as (select xmltype(to_clob(message_context))) AS messagetext FROM source)
            SELECT x.auditPartnerId,
            x.auditServiceId
            FROM src s
            , XMLTable(
            '/msgContext/OtherProperties'
            passing s.messagetext
            columns 
            auditPartnerId VARCHAR2(20) PATH 'auditPartnerId'
            , AUDITSERVICEID VARCHAR2(20) PATH 'auditServiceId'
             37  ) x;
            and getting this error:
            Error starting at line 1 in command:
            with src as (select xmltype(to_clob(message_context))) AS messagetext FROM source)
            SELECT x.auditPartnerId,
            x.auditServiceId
            FROM src s
            , XMLTable(
            '/msgContext/OtherProperties'
            passing s.messagetext
            columns 
            auditPartnerId VARCHAR2(20) PATH 'auditPartnerId'
            , AUDITSERVICEID VARCHAR2(20) PATH 'auditServiceId'
             37  ) x
            Error at Command Line:1 Column:53
            Error report:
            SQL Error: ORA-00923: FROM keyword not found where expected
            00923. 00000 -  "FROM keyword not found where expected"
            *Cause:    
            *Action:
            Edited by: alekons on Feb 20, 2012 8:17 AM
            • 3. Re: Select fields from XML in CLOB column
              odie_63
              There's a superfluous parenthesis :
              with src as (select xmltype(to_clob(message_context)) /*)*/ AS messagetext FROM source)
              But you do not need TO_CLOB nor using the WITH clause here, just query from your base table passing the XMLType constructor directly :
              SELECT x.auditPartnerId,
                     x.auditServiceId
              FROM source s
                 , XMLTable(
                     '/msgContext/OtherProperties'
                     passing xmltype(s.your_clob_column)
                     columns 
                       auditPartnerId   VARCHAR2(20) PATH 'auditPartnerId'
                     , auditServiceId   VARCHAR2(20) PATH 'auditServiceId'
                   ) x
              ;
              BTW, as you posted it, this is not a valid XML prolog :
              <?xml version=1.0 encoding=UTF-8?>
              It should be
              <?xml version="1.0" encoding="UTF-8"?>
              or,
              <?xml version='1.0' encoding='UTF-8'?>
              Edited by: odie_63 on 20 févr. 2012 18:17
              • 4. Re: Select fields from XML in CLOB column
                698705
                I change the XML header to
                <?xml version="1.0" encoding="UTF-8"?>
                And now i am getting the following error:
                Error report:
                SQL Error: ORA-31011: XML parsing failed
                ORA-19202: Error occurred in XML processing
                LPX-00243: element attribute value must be enclosed in quotes
                Error at line 1
                ORA-06512: at "SYS.XMLTYPE", line 272
                ORA-06512: at line 1
                31011. 00000 -  "XML parsing failed"
                *Cause:    XML parser returned an error while trying to parse the document.
                *Action:   Check if the document to be parsed is valid.
                • 5. Re: Select fields from XML in CLOB column
                  odie_63
                  Could you post your XML again (the one that triggers the error, without alteration), please?

                  Give your database version as well.

                  Thanks.
                  • 6. Re: Select fields from XML in CLOB column
                    698705
                    SQL> select * from v$version ;
                    
                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
                    PL/SQL Release 11.2.0.2.0 - Production
                    CORE     11.2.0.2.0     Production
                    TNS for Linux: Version 11.2.0.2.0 - Production
                    NLSRTL Version 11.2.0.2.0 - Production
                    And the XML
                    <?xml version=1.0 encoding=UTF-8?>
                    <msgContext>
                        <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
                            <jms1:JMSDeliveryMode>PERSISTENT</jms1:JMSDeliveryMode>
                            <jms1:JMSTimestamp>1329217943352</jms1:JMSTimestamp>
                            <jms1:JMSExpiration>0</jms1:JMSExpiration>
                            <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
                            <jms1:JMSPriority>4</jms1:JMSPriority>
                        </JMSHeaders>
                        <OtherProperties>
                            <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">010000000001189CC2BC2C</auditPartnerId>
                            <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Auth</auditServiceId>
                            <auditCountryCode xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                            <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.346 +0100</auditReceivedDate>
                            <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
                            <correlationExternalId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                            <correlationBusinessProcessId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                            <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.351 +0100</auditForwardedDate>
                            <ns0:auditInfo xmlns:ns0="http://www.tibco.com/schemas/Project_reporting/tib_bw_reporting/Resources/Schemas/auditInfo.xsd">
                                <ns0:dp-logpoint>response</ns0:dp-logpoint>
                                <ns0:dp-target-url/>
                                <ns0:dp-uri>/comp_am</ns0:dp-uri>
                                <ns0:dp-size>1365</ns0:dp-size>
                                <ns0:dp-replytoengine/>
                            </ns0:auditInfo>
                        </OtherProperties>
                    </msgContext>
                    Edited by: alekons on Feb 21, 2012 4:48 AM

                    Edited by: alekons on Feb 21, 2012 4:51 AM
                    • 7. Re: Select fields from XML in CLOB column
                      odie_63
                      Now I don't understand...
                      Did you change the prolog or not?

                      If I use the following XML, it's OK :
                      <?xml version="1.0" encoding="UTF-8"?>
                      <msgContext>
                          <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
                              <jms1:JMSDestination>vf.gig.eu.preprod.gatewaynorth.messagestore</jms1:JMSDestination>
                              <jms1:JMSDeliveryMode>PERSISTENT</jms1:JMSDeliveryMode>
                              <jms1:JMSMessageID>ID:GIG-PREPROD-INBOUND.41F34E70B90F6FAF2:1038322</jms1:JMSMessageID>
                              <jms1:JMSTimestamp>1329217943352</jms1:JMSTimestamp>
                              <jms1:JMSExpiration>0</jms1:JMSExpiration>
                              <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
                              <jms1:JMSPriority>4</jms1:JMSPriority>
                          </JMSHeaders>
                          <OtherProperties>
                              <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">010000000001189CC2BC2C</auditPartnerId>
                              <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Auth</auditServiceId>
                              <auditCountryCode xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                              <correlationTransactionId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">d0022064-1df3-43ef-be5b-93aedc96b3b2</correlationTransactionId>
                              <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.346 +0100</auditReceivedDate>
                              <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
                              <auditQueueName xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">vf.gig.eu.androidmarketbilling.gatewaynorth.response</auditQueueName>
                              <correlationExternalId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                              <correlationBusinessProcessId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                              <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.351 +0100</auditForwardedDate>
                              <auditMsisdn xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                              <auditEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorthLog</auditEngineId>
                              <correlationTracking xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorth.Cache_API_instance1.defaultAuditEngineId.CompositeRestCR-1.amb1.defaultAuditEngineId.defaultAuditEngineId.dpSouth.defaultAuditEngineId.amb1.Cache_API_instance1.dpNorth</correlationTracking>
                              <ns0:auditInfo xmlns:ns0="http://www.tibco.com/schemas/Project_gig_reporting/tib_bw_reporting/Resources/Schemas/auditInfo.xsd">
                                  <ns0:dp-device>PRE_PROD_NORTH</ns0:dp-device>
                                  <ns0:dp-logpoint>response</ns0:dp-logpoint>
                                  <ns0:dp-target-url/>
                                  <ns0:dp-ip-source>85.205.40.253</ns0:dp-ip-source>
                                  <ns0:dp-port>20565</ns0:dp-port>
                                  <ns0:dp-uri>/comp_am</ns0:dp-uri>
                                  <ns0:dp-size>1365</ns0:dp-size>
                                  <ns0:dp-replytoengine/>
                              </ns0:auditInfo>
                          </OtherProperties>
                      </msgContext>
                      What are you doing differently?
                      • 8. Re: Select fields from XML in CLOB column
                        698705
                        Sorry my mistake.

                        Your solution is working perfectly !!!!!

                        Now how can i pass dynamically all the fields from xml ?
                        • 9. Re: Select fields from XML in CLOB column
                          odie_63
                          Now how can i pass dynamically all the fields from xml ?
                          Sorry, I swear I had my coffee today, but I still don't understand what you want to do :)

                          Do you want to extract elements from the XML? or something else?
                          If the former, XMLTable will do fine, just as in your first attempt.
                          • 10. Re: Select fields from XML in CLOB column
                            user13734057
                            Hi ,
                            I have a xml like this. I tried to extract fields by this query but it throws error of string literal required.
                            Please help.

                            XML is :
                            <?xml version="1.0"?>
                            <imageData imageName="IW010A0251043" imageType="PNG" version="2.0" xmlns="com.snapon.sbs"><callouts shape="roundedRectangle"><callout label="79"><point x="489" y="27"/></callout><callout label="128"><point x="624" y="41"/></callout><callout label="4"><point x="757" y="88"/></callout><callout label="43"><point x="610" y="101"/></callout><callout label="43"><point x="373" y="102"/></callout><callout label="5"><point x="694" y="118"/></callout><callout label="1"><point x="845" y="158"/></callout><callout label="128"><point x="230" y="181"/></callout><callout label="4"><point x="293" y="188"/></callout><callout label="5"><point x="358" y="217"/></callout><callout label="79"><point x="835" y="228"/></callout><callout label="43"><point x="315" y="309"/></callout><callout label="43"><point x="693" y="349"/></callout><callout label="72"><point x="378" y="400"/></callout><callout label="69"><point x="723" y="425"/></callout><callout label="94"><point x="365" y="496"/></callout><callout label="82"><point x="727" y="708"/></callout><callout label="82"><point x="272" y="823"/></callout></callouts></imageData>

                            I tried this :

                            with bl_image_data as (select xmltype(IMG_CALLOUT_DATA) AS messagetext FROM bl_image_data)
                            SELECT x.auditPartnerId,
                            x.auditServiceId
                            FROM bl_image_data s
                            , XMLTable(
                            '/imageData/callouts'
                            passing s.IMG_CALLOUT_DATA
                            columns
                            imageName VARCHAR2(20) PATH 'imageName'
                            , "callout label" VARCHAR2(20) PATH 'callout label'
                            ) x;

                            BL_IMAGE_DATA is my source table which has CLOB column IMG_CALLOUT_DATA.

                            I want data like this (in oracle table)

                            Image Name Callout label

                            IW010A0251043 79
                            IW010A0251043 128
                            IW010A0251043 4
                            ....... ...

                            and so on.

                            Please help me.
                            • 11. Re: Select fields from XML in CLOB column
                              Jason_(A_Non)
                              Please start a new thread (follow the Post New Thread link seen above all the threads in this forum).

                              When doing so, make sure to follow the instructions at
                              [url https://forums.oracle.com/forums/ann.jspa?annID=311]Announcement: Please read carefully before starting a new thread..

                              Hint: Your XML has a default namespace, so look at the [url https://forums.oracle.com/forums/ann.jspa?annID=278]XML DB FAQ Thread for how to add a default namespace to XMLTable. You also don't need the WITH. That was just used as an example to simulate a table in the example in this thread so your code will become
                              passing XMLType(s.IMG_CALLOUT_DATA)
                              That should get you going with a new post for your issues.
                              • 12. Re: Select fields from XML in CLOB column
                                muttleychess
                                Hi
                                Why tried to use your example no work for me ?
                                with src as ( select xmltype(to_clob('<msgContext>
                                <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
                                <jms1:JMSDeliveryMode>PERSISTENT</jms1:JMSDeliveryMode>
                                <jms1:JMSTimestamp>1329217943352</jms1:JMSTimestamp>
                                <jms1:JMSExpiration>0</jms1:JMSExpiration>
                                <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
                                <jms1:JMSPriority>4</jms1:JMSPriority>
                                </JMSHeaders>
                                <OtherProperties>
                                <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">010000000001189CC2BC2C</auditPartnerId>
                                <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Auth</auditServiceId>
                                <correlationTransactionId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">d0022064-1df3-43ef-be5b-93aedc96b3b2</correlationTransactionId>
                                <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.346 +0100</auditReceivedDate>
                                <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
                                <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:23.351 +0100</auditForwardedDate>
                                <auditMsisdn xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
                                <auditEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorthLog</auditEngineId>
                                <ns0:auditInfo xmlns:ns0="http://www.tibco.com/schemas/Project_gig_reporting/tib_bw_reporting/Resources/Schemas/auditInfo.xsd">
                                <ns0:dp-logpoint>response</ns0:dp-logpoint>
                                <ns0:dp-target-url/>
                                <ns0:dp-port>20565</ns0:dp-port>
                                <ns0:dp-uri>/comp_am</ns0:dp-uri>
                                <ns0:dp-size>1365</ns0:dp-size>
                                <ns0:dp-replytoengine/>
                                </ns0:auditInfo>
                                </OtherProperties>
                                </msgContext>')) as messagetext from dual )
                                SELECT x.auditPartnerId,
                                x.auditServiceId
                                FROM src s
                                , XMLTable('/msgContext/OtherProperties' passing s.messagetext  columns 
                                auditPartnerId VARCHAR2(20) PATH 'auditPartnerId'
                                , AUDITSERVICEID VARCHAR2(20) PATH 'auditServiceId'
                                 37  ) x;
                                using
                                Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
                                PL/SQL Release 9.2.0.8.0 - Production
                                CORE     9.2.0.8.0     Production
                                TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
                                NLSRTL Version 9.2.0.8.0 - Production
                                return error b below:
                                ORA-00933: SQL command not properly ended
                                Edited by: muttleychess on Aug 22, 2012 3:14 PM
                                • 13. Re: Select fields from XML in CLOB column
                                  Marco Gralike
                                  XMLTABLE was not available before Oracle Database version 10.2.0.1.0