2 Replies Latest reply: Jul 1, 2013 11:28 PM by 1021367 RSS

    xmltype column value extraction

    1021367

      Hi Team,

       

      Am new to XML Querying and would like to seek your help on the below.

       

      I have a Table - DEQUEUE_3_OUT

       

      desc DEQUEUE_3_OUT

      Name    Null Type    

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

      XML_MSG      XMLTYPE()

       

      Now my Table has a record holding the below XML -

       

      <ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/streams/schemas/lcr  http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">

        <source_database_name>ORCL.ST-IDC.IDC.ORACLE.COM</source_database_name>

        <command_type>UPDATE</command_type>

        <object_owner>XYZABC</object_owner>

        <object_name>NOTIFY_TABLE</object_name>

        <transaction_id>4.23.3135</transaction_id>

        <scn>4727635</scn>

        <old_values>

          <old_value>

            <column_name>NOTIFICATION_ID</column_name>

            <data>

              <varchar2>6</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>NOTIFICATION_TYPE</column_name>

            <data>

              <varchar2>2 ERROR</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>SOURCE_SYSTEM</column_name>

            <data>

              <varchar2>GFIPRD</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>STAGE</column_name>

            <data>

              <varchar2>DNB</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>ERROR_SOURCE</column_name>

            <data>

              <varchar2>MULTIPLE_MATCHES_LOC</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>BATCH_ID</column_name>

            <data>

              <number>3</number>

            </data>

          </old_value>

          <old_value>

            <column_name>BATCH_DESC</column_name>

            <data>

              <varchar2>GFIPRD</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>ASSIGNED_TO</column_name>

            <data>

              <varchar2>OLD_USER</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>PRIORITY</column_name>

            <data>

              <number>3</number>

            </data>

          </old_value>

          <old_value>

            <column_name>STATUS_CODE</column_name>

            <data>

              <varchar2>NEW</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>ESCALATION_REQD</column_name>

            <data>

              <varchar2 xsi:nil="true"/>

            </data>

          </old_value>

          <old_value>

            <column_name>ESCALATION_LEVEL</column_name>

            <data>

              <number xsi:nil="true"/>

            </data>

          </old_value>

          <old_value>

            <column_name>NEED_ENRICH_DATA</column_name>

            <data>

              <varchar2>Y</varchar2>

            </data>

          </old_value>

          <old_value>

            <column_name>COMMENTS</column_name>

            <data>

              <varchar2 xsi:nil="true"/>

            </data>

          </old_value>

          <old_value>

            <column_name>CREATED_BY</column_name>

            <data>

              <varchar2 xsi:nil="true"/>

            </data>

          </old_value>

          <old_value>

            <column_name>CREATION_DATE</column_name>

            <data>

              <timestamp>

                <value/>

                <format>SYYYY/MM/DD HH24:MI:SSXFF9</format>

              </timestamp>

            </data>

          </old_value>

          <old_value>

            <column_name>LAST_UPDATED_BY</column_name>

            <data>

              <varchar2 xsi:nil="true"/>

            </data>

          </old_value>

          <old_value>

            <column_name>LAST_UPDATE_DATE</column_name>

            <data>

              <timestamp>

                <value/>

                <format>SYYYY/MM/DD HH24:MI:SSXFF9</format>

              </timestamp>

            </data>

          </old_value>

          <old_value>

            <column_name>EMAIL_FLAG</column_name>

            <data>

              <varchar2 xsi:nil="true"/>

            </data>

          </old_value>

          <old_value>

            <column_name>CREATED_BY_MODULE</column_name>

            <data>

              <varchar2 xsi:nil="true"/>

            </data>

          </old_value>

        </old_values>

        <new_values>

          <new_value>

            <column_name>ASSIGNED_TO</column_name>

            <data>

              <varchar2>NEW_USER</varchar2>

            </data>

          </new_value>

        </new_values>

      </ROW_LCR>

       

      I need to Query such a way that i need the below Output

       

      Column Name             Old_Value           New_Value

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

      NOTIFICATION_ID        6                         NULL

      ASSIGNED_TO           OLD_USER         NEW_USER

       

      Many Thanks In Advance.

        • 1. Re: xmltype column value extraction
          odie_63

          You probably want to use a FULL OUTER JOIN in this case :

           

          SQL> with old_values as (

            2    select x.column_name, x.old_value

            3    from dequeue_3_out t

            4       , xmltable(

            5           xmlnamespaces(default 'http://xmlns.oracle.com/streams/schemas/lcr')

            6         , 'for $old in /ROW_LCR/old_values/old_value

            7            let $val := $old/data/*

            8            return element r {

            9              $old/column_name

          10            , element old_value {

          11                typeswitch ($val)

          12                  case element(timestamp) return $val/value

          13                  default return $val

          14              }

          15            }'

          16           passing t.xml_msg

          17           columns column_name varchar2(30)   path 'column_name'

          18                 , old_value   varchar2(4000) path 'old_value'

          19         ) x

          20  ),

          21  new_values as (

          22   select x.column_name, x.new_value

          23   from dequeue_3_out t

          24      , xmltable(

          25          xmlnamespaces(default 'http://xmlns.oracle.com/streams/schemas/lcr')

          26        , 'for $new in /ROW_LCR/new_values/new_value

          27           let $val := $new/data/*

          28           return element r {

          29             $new/column_name

          30           , element new_value {

          31               typeswitch ($val)

          32                 case element(timestamp) return $val/value

          33                 default return $val

          34             }

          35           }'

          36          passing t.xml_msg

          37          columns column_name varchar2(30)   path 'column_name'

          38                , new_value   varchar2(4000) path 'new_value'

          39        ) x

          40  )

          41  select nvl(old.column_name, new.column_name) as column_name

          42       , old.old_value

          43       , new.new_value

          44  from old_values old full outer join new_values new

          45       on old.column_name = new.column_name

          46  ;

           

          COLUMN_NAME                    OLD_VALUE              NEW_VALUE

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

          ASSIGNED_TO                    OLD_USER               NEW_USER

          NEED_ENRICH_DATA               Y                     

          STAGE                          DNB                   

          ESCALATION_LEVEL                                     

          BATCH_DESC                     GFIPRD                

          LAST_UPDATED_BY                                      

          ESCALATION_REQD                                      

          STATUS_CODE                    NEW                   

          SOURCE_SYSTEM                  GFIPRD                

          EMAIL_FLAG                                           

          NOTIFICATION_TYPE              2 ERROR               

          CREATED_BY_MODULE                                    

          COMMENTS                                             

          LAST_UPDATE_DATE                                     

          CREATION_DATE                                        

          NOTIFICATION_ID                6                     

          PRIORITY                       3                     

          BATCH_ID                       3                     

          CREATED_BY                                           

          ERROR_SOURCE                   MULTIPLE_MATCHES_LOC  

           

          20 rows selected

          • 2. Re: xmltype column value extraction
            1021367

            Thank you so much, it helped me a lot.