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?

Its all XMl nodes.. XYZ ..How can i get a value of particular node say — Anandan, Sep 01 '14 at 06:45
See my previous [answer](http://stackoverflow.com/a/25400877/3686755) — neshkeev, Sep 01 '14 at 08:02
Great way to do it..but marking the other as correct as he responded first. Thanks everyone — Anandan, Sep 01 '14 at 10:14

score 1 · Accepted Answer · answered Sep 01 '14 at 08:07

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

Get part of message from LOB content

1 Answers1