This discussion is archived
2 Replies Latest reply: Jul 1, 2013 9:28 PM by b1b06270-18dd-4134-bb87-6dbcc3c3628e RSS

xmltype column value extraction

b1b06270-18dd-4134-bb87-6dbcc3c3628e Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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
    b1b06270-18dd-4134-bb87-6dbcc3c3628e Newbie
    Currently Being Moderated

    Thank you so much, it helped me a lot.

Legend

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