2 Replies Latest reply: Oct 23, 2013 8:44 AM by Samuel Rabini RSS

    list 1 level node name

    Samuel Rabini

      Hi,

       

      I'm working on an Oracle Database 11g Enterprise Edition Release 11.2.0.3.0.

      I've the table t_type_asset_configuration(i_type_asset_configuration number, asset_setting varchar2, value xmltype) that currently has 2 records:

       

      1,'cma_layout',

      "<value>

         <search>vehicle</search>

         <create_new>vehicle</create_new>

         <view>vehicle</view>

         <list>vehicle</list>

      </value>"

       

       

      1,'fma_layout',

      "<value>

         <fields>

            <field label="fma_asset_name" action="concatenate1">

               <column postfix=" - ">alias</column>

               <column>brand_vehicle</column>

               <column>model</column>

               <column style="bold">plate_number</column>

            </field>

            <field label="fma_user">vehicle_user</field>

            <field label="fma_last_contact">datetime</field>

            <field label="fma_address" action="concatenate">

               <column postfix=" - " style="bold">spacetarget</column>

               <column>address</column>

            </field>

            <field label="fma_odo" postfix=" km">mileage_counter</field>

            <field label="fma_ign">ignition</field>

            <field label="fma_group">group_name</field>

            <field label="fma_event">has_recent_alarms</field>

            <field label="fma_info">id_data</field>

            <other_field>

               <field>id_vehicle</field>

               <field>id_user</field>

               <field>id_device</field>

               <field>id_asset</field>

               <field>id_vehicle_user</field>

               <field>id_data_address</field>

               <field>id_data_datetime</field>

               <field>id_data_last_position</field>

               <field>id_space_target</field>

               <field>id_group</field>

               <field>has_hour_counter</field>

               <field>latitude</field>

               <field>longitude</field>

            </other_field>

         </fields>

         <query>pk_evolution.get_vehicle_list</query>

      </value>"

       

      My goal is to create a view with i_type_asset_configuration, asset_setting, module, module_value, where module is the 1st level node name (nodes under <value> node) and module_value is the inner text.

       

      I wrote this query:

       

        SELECT c.i_type_asset_configuration,

                      c.asset_setting,

                      x.object_value.getrootelement() module

          FROM t_type_asset_configuration c,

                    XMLTABLE('//*' passing c.value) x

         WHERE c.asset_setting            in ('cma_layout','fma_layout')

           AND x.existsNode( '/*/*' )       = 0 ;

       

      that works for the cma_layout, in fact I get the recordset:

       

      1    cma_layout    search

      1    cma_layout    create_new

      1    cma_layout    view

      1    cma_layout    list

       

      but not for the fma_layout one, getting:

       

      1    fma_layout    column

      1    fma_layout    column

      1    fma_layout    column

      1    fma_layout    column

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    column

      1    fma_layout    column

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    field

      1    fma_layout    query

       

      The goal is to have for fma_layout:

       

      1    fma_layout   fields

      1    fma_layout   query

       

      Do you have any suggestion on how stop the list to the 1st level node?

       

      Thanks in advance,

      Samuel

        • 1. Re: list 1 level node name
          odie_63

          Hi,

           

          Do not use a descendant axis if you don't want to see descendants.

          Besides, getRootElement() and existsNode() are deprecated now.

           

          Here's one way :

          SQL> select t.i_type_asset_configuration

            2       , t.asset_setting

            3       , x.module

            4       , x.module_value

            5  from t_type_asset_configuration t

            6     , xmltable(

            7         '/value/*'

            8         passing t.value

            9         columns module       varchar2(30)   path 'local-name(.)'

          10               , module_value varchar2(2000) path 'text()'

          11       ) x

          12  ;

           

          I_TYPE_ASSET_CONFIGURATION ASSET_SETTING    MODULE       MODULE_VALUE

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

                                   1 cma_layout       search       vehicle

                                   1 cma_layout       create_new   vehicle

                                   1 cma_layout       view         vehicle

                                   1 cma_layout       list         vehicle

                                   1 fma_layout       fields      

                                   1 fma_layout       query        pk_evolution.get_vehicle_list

           

          6 rows selected

           

          • 2. Re: list 1 level node name
            Samuel Rabini

            Thanks,

             

            it works as needed.

             

            Samuel