Using the Oracle XMLType

Created 15 July 2005 00:00

Getting the value of an xml element in a CLOB

In this example, xml is stored in a CLOB, and you want to retrieve the value of a certain element:

  SELECT EXTRACTVALUE(VALUE(xml), '/your/xpath/query')
  INTO yourVarChar
  FROM TABLE(XMLSEQUENCE(XMLTYPE(rec.manual_xml).EXTRACT('/'))) xml;

Updating an XML element stored as a CLOB

This is the reverse of the previous example - if you want to update the value of the same XML element stored in a CLOB field:

  UPDATE YourTable t 
  SET t.xml_field = UPDATEXML(XMLTYPE(t.xml_field), '/your/xpath/query/text()', 'new_value').getClobVal()
  WHERE = 100;