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.