2 Replies Latest reply on Aug 10, 2009 1:44 PM by MichaelS

    How to extract data from CLOB Datatype having XML values

    601989
      Hi,

      I am facing problem in extracting data from a TAble FCT_A where OBJECT_CONTENT field(Datatype CLOB) is having data of XML type.

      Below are the value:

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      <ras-cube>
      <jndiDataSourceName>datasource_etl</jndiDataSourceName>
      <dimensions class="vector">
      <string>CUG_IND</string>
      <string>EVENT_DATE</string>
      <string>EVENT_DIRECTION_KEY</string>
      <string>EVENT_TIME_SLOT_KEY</string>
      <string>EVENT_TYPE_KEY</string>
      <string>FAF_IND</string>
      <string>FILTERED_OUT_FLAG</string>
      <string>IN_TG_ID_KEY</string>
      <string>LONG_EVENT_IND</string>
      <string>NE_ID_KEY</string>
      <string>NODE_ADDRESS</string>
      <string>OTHER_MSISDN_DIAL_DIGIT_KEY</string>
      <string>OUT_TG_ID_KEY</string>
      <string>RATING_DELAY_IND</string>
      <string>RI_MISMATCH_IND</string>
      <string>SERVED_MSISDN_DIAL_DIGIT_KEY</string>
      <string>SERVED_MSRN_DIAL_DIGIT_KEY</string>
      <string>SRV_TYPE_KEY</string>
      <string>SUBS_BU_KEY</string>
      <string>SYS_ID_KEY</string>
      <string>TERMINATION_REASON_KEY</string>
      <string>THIRD_PARTY_DIAL_DIGIT_KEY</string>
      <string>ZERO_FLAG_KEY</string>
      </dimensions>
      <measures class="vector">
      <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <targetName>CHARGE</targetName>
      <expression>SUM(FCT_RATED.CHARGE)</expression>
      <persist>true</persist>
      </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <targetName>COMPUTED_VOLUME</targetName>
      <expression>SUM(FCT_RATED.COMPUTED_VOLUME)</expression>
      <persist>true</persist>
      </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <targetName>DOWNLINK_VOLUME</targetName>
      <expression>SUM(FCT_RATED.DOWNLINK_VOLUME)</expression>
      <persist>true</persist>
      </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <targetName>ORIGINAL_DUR</targetName>
      <expression>SUM(FCT_RATED.ORIGINAL_DUR)</expression>
      <persist>true</persist>
      </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <targetName>RA_CHARGE</targetName>
      <expression>SUM(FCT_RATED.RA_CHARGE)</expression>
      <persist>true</persist>
      </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <targetName>RECORD_COUNT</targetName>
      <expression>COUNT(FCT_RATED.*)</expression>
      <persist>true</persist>
      </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      <targetName>UPLINK_VOLUME</targetName>
      <expression>SUM(FCT_RATED.UPLINK_VOLUME)</expression>
      <persist>true</persist>
      </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
      </measures>
      <dimensionMap class="linked-hash-map">
      <entry>
      <string>FCT_RATED</string>
      <null/>
      </entry>
      </dimensionMap>
      <cubeStorageConfig>
      <partitionColumn>EVENT_DATE</partitionColumn>
      <tableSpaceName>ORV5_ETL_DFLT</tableSpaceName>
      <isLogging>false</isLogging>
      <isCompressed>false</isCompressed>
      <noOfHashPartition>10</noOfHashPartition>
      <partitionScheme>1</partitionScheme>
      </cubeStorageConfig>
      <cubeType>1</cubeType>
      <name>MX_RATED</name>
      <label></label>
      <parentNames>
      <string>FCT_RATED</string>
      </parentNames>
      <otherProperties class="linked-hash-map"/>
      </ras-cube>

      I want to extract expression tag in the above XML types

      Kindly any help will be needful for me

      Thanks and Regards
        • 1. Re: How to extract data from CLOB Datatype having XML values
          Beijing
          9i
          with FCT_A as (
          select xmltype('
          <ras-cube>
          <jndiDataSourceName>datasource_etl</jndiDataSourceName>
          <dimensions class="vector">
          <string>CUG_IND</string>
          <string>EVENT_DATE</string>
          <string>EVENT_DIRECTION_KEY</string>
          <string>EVENT_TIME_SLOT_KEY</string>
          <string>EVENT_TYPE_KEY</string>
          <string>FAF_IND</string>
          <string>FILTERED_OUT_FLAG</string>
          <string>IN_TG_ID_KEY</string>
          <string>LONG_EVENT_IND</string>
          <string>NE_ID_KEY</string>
          <string>NODE_ADDRESS</string>
          <string>OTHER_MSISDN_DIAL_DIGIT_KEY</string>
          <string>OUT_TG_ID_KEY</string>
          <string>RATING_DELAY_IND</string>
          <string>RI_MISMATCH_IND</string>
          <string>SERVED_MSISDN_DIAL_DIGIT_KEY</string>
          <string>SERVED_MSRN_DIAL_DIGIT_KEY</string>
          <string>SRV_TYPE_KEY</string>
          <string>SUBS_BU_KEY</string>
          <string>SYS_ID_KEY</string>
          <string>TERMINATION_REASON_KEY</string>
          <string>THIRD_PARTY_DIAL_DIGIT_KEY</string>
          <string>ZERO_FLAG_KEY</string>
          </dimensions>
          <measures class="vector">
          <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <targetName>CHARGE</targetName>
          <expression>SUM(FCT_RATED.CHARGE)</expression>
          <persist>true</persist>
          </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <targetName>COMPUTED_VOLUME</targetName>
          <expression>SUM(FCT_RATED.COMPUTED_VOLUME)</expression>
          <persist>true</persist>
          </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <targetName>DOWNLINK_VOLUME</targetName>
          <expression>SUM(FCT_RATED.DOWNLINK_VOLUME)</expression>
          <persist>true</persist>
          </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <targetName>ORIGINAL_DUR</targetName>
          <expression>SUM(FCT_RATED.ORIGINAL_DUR)</expression>
          <persist>true</persist>
          </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <targetName>RA_CHARGE</targetName>
          <expression>SUM(FCT_RATED.RA_CHARGE)</expression>
          <persist>true</persist>
          </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <targetName>RECORD_COUNT</targetName>
          <expression>COUNT(FCT_RATED.*)</expression>
          <persist>true</persist>
          </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          <targetName>UPLINK_VOLUME</targetName>
          <expression>SUM(FCT_RATED.UPLINK_VOLUME)</expression>
          <persist>true</persist>
          </com.connectiva.onereview.rasobjects.cube.CubeMeasure>
          </measures>
          <dimensionMap class="linked-hash-map">
          <entry>
          <string>FCT_RATED</string>
          <null/>
          </entry>
          </dimensionMap>
          <cubeStorageConfig>
          <partitionColumn>EVENT_DATE</partitionColumn>
          <tableSpaceName>ORV5_ETL_DFLT</tableSpaceName>
          <isLogging>false</isLogging>
          <isCompressed>false</isCompressed>
          <noOfHashPartition>10</noOfHashPartition>
          <partitionScheme>1</partitionScheme>
          </cubeStorageConfig>
          <cubeType>1</cubeType>
          <name>MX_RATED</name>
          <label></label>
          <parentNames>
          <string>FCT_RATED</string>
          </parentNames>
          <otherProperties class="linked-hash-map"/>
          </ras-cube>') OBJECT_CONTENT from dual
          )
          SELECT   EXTRACTVALUE(value(d), '//expression/text()', '') exp
          FROM     fct_a t,
                   TABLE(XMLSEQUENCE(EXTRACT (
                                       t.object_content,
                                       '//ras-cube/measures/com.connectiva.onereview.rasobjects.cube.CubeMeasure/expression'
                                       , ''
                                     ))) d
          Edited by: Beijing on Aug 10, 2009 9:21 AM
          • 2. Re: How to extract data from CLOB Datatype having XML values
            MichaelS
            10g:
            SELECT   x.*
            FROM     fct_a t,
                     xmltable('//expression' passing t.object_content
                     columns expression varchar2(20) path '.') x
            /
            EXPRESSION          
            --------------------
            SUM(FCT_RATED.CHARGE
            SUM(FCT_RATED.COMPUT
            SUM(FCT_RATED.DOWNLI
            SUM(FCT_RATED.ORIGIN
            SUM(FCT_RATED.RA_CHA
            COUNT(FCT_RATED.*)  
            SUM(FCT_RATED.UPLINK
            
            7 rows selected.