Anandan Anandan - 4 months ago 7
SQL Question

Get part of message from LOB content

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

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
Comments