Oracle clob read value of a field paramater

Question

I have an xml like below. There are many nodes b4 and after this but they are irrelevant.

<Parent>
    <ServiceTag>sometag</ServiceTag>
    <Addl_Payloads>
    <Addl_PayloadCount>1</Addl_PayloadCount>
        <Addl_Payload>
            <Attrib Name="TYPE" Seq="1" Value="LICENSE"> </Attrib>
            <Attrib Name="ENTITLEMENT_ID" Value="sdfsfdsadfaasdfsadf1"></Attrib>
     </Addl_Payloads>
</Parent>

How can I build a query in Oracle 11g to read values only when there is Attrib name=ENTITLEMENT_ID. I tried something like this but this gives me value column empty basically I want to read = 'sdfsfdsadfaasdfsadf1' if it exists

SELECT s.doc_id,
       extractValue(x.column_value, '/Parent/ServiceTag') as ST,
       extractValue(x.column_value, 
                   '/Parent/Addl_Payloads/Addl_Payload/Attrib[@Name="ENTITLEMENT_ID"]') as Value
 from LKMLOG.LKM_ORIG_ASB_MSG s
    , TABLE(
            XMLSequence(
                        xmltype( s.ASB_XML).extract(
                           '//Payload/PPIDInfoRequestMessage/PPIDData/Parent'
                            )
                       )  
         ) x
where  s.doc_id in (somevalues);

Answer 1

If you want to extract, if I understood you correctly, Values only when Name attribute of a node equal to "ENTITLEMENT_ID", you could do it as follows:

with t1(xml_col) as(
   select xmltype('
      <Addl_Payloads>
           <Addl_PayloadCount>1</Addl_PayloadCount>
           <Addl_Payload>
              <Attrib Name="TYPE" Seq="1" Value="LICENSE"> </Attrib>
              <Attrib Name="ENTITLEMENT_ID" Value="sdfsfdsadfaasdfsadf1"> </Attrib>
           </Addl_Payload>
       </Addl_Payloads>') from dual
  )
  select val
    from t1 t
    cross join xmltable('/Addl_Payloads/Addl_Payload/Attrib[@Name="ENTITLEMENT_ID"]'
                        passing t.xml_col
                        columns val varchar2(101) path '@Value')

Result:

 VAL
 -----------------------
 sdfsfdsadfaasdfsadf1

SqlFiddle Demo

Note: Starting from Oracle 11gr2 XMLSequence function is deprecated - still there for backward compatibility.

---

Addendum

In order to include the value of <ServiceTag> the above query could be changed the following way:

with t1(xml_col) as(
   select xmltype('
      <Parent>
        <ServiceTag>sometag</ServiceTag>
        <Addl_Payloads>
             <Addl_PayloadCount>1</Addl_PayloadCount>
             <Addl_Payload>
                <Attrib Name="TYPE" Seq="1" Value="LICENSE"> </Attrib>
                <Attrib Name="ENTITLEMENT_ID" Value="sdfsfdsadfaasdfsadf1"> </Attrib>
                <Attrib Name="ENTITLEMENT_ID" Value="another_eaxmple"> </Attrib> 
             </Addl_Payload>
         </Addl_Payloads>
      </Parent>') from dual
  )
  select q.ST
       , s.val
    from t1 t
    left join xmltable('/Parent/ServiceTag'
                        passing t.xml_col
                        columns ST varchar2(101) path '.') q
     on (1=1)    
    left join xmltable('/Parent/Addl_Payloads/Addl_Payload/Attrib[@Name="ENTITLEMENT_ID"]'
                        passing t.xml_col
                        columns val varchar2(101) path '@Value') s
      on (1=1)

Result:

ST            Val 
--------------------------
sometag       sdfsfdsadfaasdfsadf1 
sometag       another_eaxmple 

SqlFiddle Demo