Get part of message from LOB content

Question

I have some XML content(less than 2000 bytes) in a LOB (Large OBject) column. I have used dbms_lob_substr(messagebody) to get the actual XML content but I need to get the exact value of a particular node.

eg.

<first name>xyz</first name>
<last name>abcd</last name>

I need the value of say <first name>. How can I achieve this?

Answer 1

Your XML seems to be missing a root node? If your XML data is as you show it, you can get XMLTable() function to parse it if you put it in a dummy root node, for example like this:

select x.firstname
  from mytable,
  xmltable(
   '/rootnode'
   passing xmltype('<rootnode>'||myclobcol||'</rootnode>')
   columns
      firstname varchar2(100) path 'firstname'
  ) x

If you need to get multiple values at the same time, you just add to the columns clause:

select x.*
  from mytable,
  xmltable(
   '/rootnode'
   passing xmltype('<rootnode>'||myclobcol||'</rootnode>')
   columns
      firstname varchar2(100) path 'firstname',
      lastname  varchar2(100) path 'lastname',
      middleini varchar2(10)  path 'middleinitial'
  ) x