Extracting value from CLOB XML column

Question

I have a table where one of the columns is of type CLOB which has XML formatted data.

I am trying to extract value of CreatedByUserID which is 7934665 in the below example.

I tried doing it with XPATH and extractvalue functions in Oracle but for some reason I am not getting result.

Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()

It says LPX-00601: Invalid token in. Can you please help me on this.

Column Value:

<EventPayloadDetails 
  xmlns:bpmn="http://schemas.oracle.com/bpm/xpath" 
  xmlns:ns3="urn:qi.com/EventPayloadDetails"  
  xmlns="urn:qi.com/EventPayloadDetails">
    <ns3:PayloadDetailsList>
        <ns3:PayloadDetail>
            <ns3:key>NUMBER</ns3:key>
            <ns3:value>0014</ns3:value>
        </ns3:PayloadDetail>
        <ns3:PayloadDetail>
            <ns3:key>Dep Num</ns3:key>
            <ns3:value>120</ns3:value>
        </ns3:PayloadDetail>
        <ns3:PayloadDetail>
            <ns3:key>CreatedByUserID</ns3:key>
            <ns3:value>7934665</ns3:value>
        </ns3:PayloadDetail>
        <ns3:PayloadDetail>
            <ns3:key>CreatedByFirstName</ns3:key>
            <ns3:value>M</ns3:value>
        </ns3:PayloadDetail>
        <ns3:PayloadDetail>
            <ns3:key>CreatedByLastName</ns3:key>
            <ns3:value>Ash</ns3:value>
        </ns3:PayloadDetail>
        <ns3:PayloadDetail>
            <ns3:key>Comments</ns3:key>
            <ns3:value>"Review after this 
query"
</ns3:value>
        </ns3:PayloadDetail>
    </ns3:PayloadDetailsList>
</EventPayloadDetails>

Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal() — san
Please edit your question instead of putting additional information as comment. — Wernfried Domscheit

artbro artbro · Accepted Answer · 2019-03-07T13:24:20

Created a query which takes your XML (in WITH part) and performs extracts (scroll to the end).

WITH test_table
  AS (SELECT TO_CLOB('<EventPayloadDetails 
     xmlns:bpmn="http://schemas.oracle.com/bpm/xpath" 
     xmlns:ns3="urn:qi.com/EventPayloadDetails"  
     xmlns="urn:qi.com/EventPayloadDetails">
       <ns3:PayloadDetailsList>
           <ns3:PayloadDetail>
               <ns3:key>NUMBER</ns3:key>
               <ns3:value>0014</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>Dep Num</ns3:key>
               <ns3:value>120</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>CreatedByUserID</ns3:key>
               <ns3:value>7934665</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>CreatedByFirstName</ns3:key>
               <ns3:value>M</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>CreatedByLastName</ns3:key>
               <ns3:value>Ash</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>Comments</ns3:key>
               <ns3:value>"Review after this 
   query"
   </ns3:value>
           </ns3:PayloadDetail>
       </ns3:PayloadDetailsList>
   </EventPayloadDetails>') as test_data
   from dual)
SELECT XMLTYPE(test_data) xml_data
     , EXTRACTVALUE(
          XMLTYPE(test_data)
         ,'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value'
         ,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
       ) value
     , XMLTYPE(test_data).extract(
                             '//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value/text()'
                            ,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
                          ).getStringVal() value2
  FROM test_table

Extracting value from CLOB XML column

2 Answers