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!

select xml element attribute of nested elements from a string

user598242Jan 18 2018 — edited Jan 18 2018

I want to select xml element attribute value from a element and its nested element. for example,

I have this

X XMLTYPE := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>

<request>

    <serviceOrderItems>

        <serviceOrderItem specId="108">

            <entitlements>

                <entitlement id="55"/>

                <entitlement id="990"/>

            </entitlements>

        </serviceOrderItem>

        <serviceOrderItem specId="118">

            <entitlements>

                <entitlement id="551"/>

                <entitlement id="991"/>

            </entitlements>

        </serviceOrderItem>

    </serviceOrderItems>

</request>');

I want to select specId and the id, the result is:

specId   Id

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

108     55

108    990

118    551

118    991

I know that by using this I can get specId.

SELECT ExtractValue(Value(p),'/serviceOrderItem/@specId') as specId

        FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//request/serviceOrderItems/serviceOrderItem'))) P)

How do I get specId and Id? Thanks in advance.

This post has been answered by Paulzip on Jan 18 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 15 2018
Added on Jan 18 2018
6 comments
471 views