Extracting a specific child node in Oracle sQL with EXTRACTVALUE (or other methods)

Question

Writing a query to parse an XML clob, and need to return a specific child node where a node is = 375. The XML in question:

table is F1_MKTMSG_IN_DATA column is MKTMSG_ID, which contains the below XML (sample record)

<root>
<MKT_MSG_DATA>
  <receivedDetail>
    <rpdtGrp>
      <rpdt>
        <rpdt>375</rpdt>
        <rpd>2023-11-05</rpd>
      </rpdt>
      <rpdt>
        <rpdt>656</rpdt>
        <rpd>2023-11-05</rpd>
      </rpdt>
      <rpdt>
        <rpdt>MRR</rpdt>
        <rpd>2023-11-05</rpd>
      </rpdt>
    </rpdtGrp>
    <rpdrGrp/>
    <rpaGrp/>
    <mdlGrp/>
    <udlGrp/>
    <cdGrp/>
  </receivedDetail>
</MKT_MSG_DATA>
</root>

I need to select the <rpd>2023-11-05</rpd> where the <rpdt> = 375. Since I dont believe its actually the parent, I suppose I would just need to grab the rpdt parent that contains rpdt = 375, and then grab the rpd value from that, but im not sure if EXTRACTVALUE has that capability?

Ive tried using

select EXTRACTVALUE(XMLTYPE('<root>' || MKT_MSG_DATA || '</root>'), 'root/MKT_MSG_DATA/receivedDetail/rpdtGrp[1]/rpdt[1]/rpd[1]') FROM F1_MKTMSG_IN_DATA

Which will get me the correct values, but only if the xml is in that specific order, I cant be sure if it will always come in that order so I need it to actually find specific values. ALso ive heard extractvalue has long since been deprecated so I would prefer using something else

Answer 1

You can change your XPath to filter on a child node value:

'root/MKT_MSG_DATA/receivedDetail/rpdtGrp/rpdt[rpdt="375"]/rpd'

I'm not sure why you're adding the root node; your example value already has it, but presumably your real data doesn't - but as the MKT_MSG_DATA is a valid fragment on its own adding root doesn't really seem to achieve anything.

Also note that EXTRACTVALUE has been deprecated for a long time, and you can use XMLQuery instead:

select xmlquery('/MKT_MSG_DATA/receivedDetail/rpdtGrp/rpdt[rpdt="375"]/rpd/text()'
    passing XMLType(MKT_MSG_DATA)
    returning content).getStringVal() as result
from F1_MKTMSG_IN_DATA

And you can convert that returned value to a native date if you want.

Or you can use XMLTable, which is handy for getting multiple values at once, but also makes it easy to convert data to the right type:

select x.rpd
from F1_MKTMSG_IN_DATA
cross apply XMLTable('/MKT_MSG_DATA/receivedDetail/rpdtGrp/rpdt[rpdt="375"]'
  passing XMLType(MKT_MSG_DATA)
  columns rpd date path 'rpd'
) x

fiddle