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);

Nick Krasnov · Answer 1 · 2013-09-10T19:50:00.670

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

Nicholas - thanks for the quick response... I am new to Oracle XML so may not have provided all the information. The XML is a clob (a huge XML) you are absolutely right about what I am trying to get. I want to 'Value' from only if Name="ENTITLEMENT_ID" As the XML is huge and I am dealing with multiple rows. is there something I can do within the format of what I pasted? — RaoLinMau, Sep 09 '13 at 21:02
@RaoLinMau `is there something I can do within the format of what I pasted` I'm not sure I'm following. What format are we talking about? Please edit your question to add some clarity. — Nick Krasnov, Sep 09 '13 at 21:13
I added in the oroginal XML .. I need the service tag and value of attrib if name=entitlement_id ST = sometag and value=sdfsfdsadfaasdfsadf1 — RaoLinMau, Sep 10 '13 at 03:10

Oracle clob read value of a field paramater

1 Answers1