Skip to main content

Content Extracting From 'CLOB' type Columns in SQL Querries

Extracting XML node value from CLOB  column in data base.

Syntax

ExtractValue(xml_fragment, xpath_expression)

Query Syntax

Select Extractvalue(XMLtype('<column name>'),'<XML node path>')
From <table list>
Where <conditions>

Example

Select Extractvalue(Xmltype(emp_xml), '/employees/empployee/emp_number')
From employee 
where  Status='A';

Updating a XML node value from CLOB  column in data base.

Syntax


UpdateXML(xml_target, xpath_expr, new_xml)

Query Syntax

Update table_name Set column_name=Updatexml(Xmltype('<column name>'),'<XML node path>','<Replacing XMLvalue'>).getclobval
From <table list>
Where <conditions>;

Example

Initial value /employees/empployee/emp_name/text() = Bhargava

Update employe Set emp_name = Updatexml(Xmltype(emp_xml), '/employees/empployee/emp_name/text()','Bhargava Surimenu')
where  emp_no=1207;

Comments

Popular posts from this blog

NO SQL Types and vendors in market

NO SQL

KEY - VALUE Store  - Similar to a  MAP.
   e.g. Dynamo DB or REDIS

DOCUMENT Sore  - Similar to KEY-VALUE, value can be JSON/XML and key will be unqiuely idnetified this document.
   e.g. Coutch DB or mongoDB.

Column Store - Multi timentional table, identifying data with row and column numbers.
  e.g.  Cassandra or Apache HBase

Graph Store - Store relation between nodes (record entities). It has better transaction management
e.g. Neo4J and Orient DB.




Spring Reactive Stack