Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extract XML Values - order by element

3174034Apr 21 2016 — edited Apr 21 2016

Hi All,

I want to extract the XML values in the order, how the XML elements are presented. See the sample XML below. the number of elements <GroupOperator> and <properties> are unknown and dynamically generated. I am generating WHERE clause Query using this tags.

I am able to extract values using XMLTABLE() for both, but the order is the problem here. Expected output is like below.Many Thanks.

Note: <GroupOperator> and <properties> elements are come in any order, i.e <properties> element may come after <GroupOperator> and vice versa.


WHERE (country = Austria AND cn_id=000821)  -- <Group operator> values

     AND generic_name = ABT-510         -- <properties> Values

    AND  generic_name1= ABT-5101


              <SearchCriteria>

                                <sortOrder></sortOrder>

                                <LogicalOperator>AND</LogicalOperator>

                                <recordLimit>1000</recordLimit>

                                <GroupOperator>

                                    <GroupOperatorValue>AND</GroupOperatorValue>

                                     <properties>

                                        <propertySourceName>country</propertySourceName>

                                        <Operator>=</Operator>

                                        <value>Austria</value>

                                        </properties>

                                     <properties>

                                       <propertySourceName>cn_id</propertySourceName>

                                       <Operator>=</Operator>

                                       <value>000821</value>

                                     </properties> 

                                 </GroupOperator>

                                <properties>

                                    <propertySourceName>generic_name</propertySourceName>

                                    <Operator>=</Operator>

                                    <value>ABT-510</value>

                                </properties> 

                                 <properties>

                                     <propertySourceName>generic_name1</propertySourceName>

                                     <Operator>=</Operator>

                                     <value>ABT-5101</value>

                                </properties>

         </SearchCriteria>'

Comments

DeepC

Use Database Link to move tables data or COPY command then indexes, views etc.. can be created on the target using the sql file(INDEXFILE) created by imp command

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 19 2016
Added on Apr 21 2016
8 comments
1,416 views