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

Maven Build Life Cyles

Following are the maven build life cycles.

Clean - Removes the generated files in 'target' folder.

command : mvn clean

Validate - It validates your project and verify all the necessary information is there in project or not.

mvn  valiate

Compile - It compiles your project code.

mvn compile

Package - It takes the compiled code and packaged to JAR/WAR/EAR. It also compiles your code if your code is not compiled.

mvn package

Install -   It takes the packaged code and puts in your local maven repository (M2_HOME). It also does the compile and package if these are not already been done.

mvn install

Deploy - It takes the package and puts in remote repository for sharing accross team members or for other projects.

mvn deploy


FYI - Above commands can be used as variety of combinations.
e.g. mvn clean install
mvn clean package
mvn clean compile package install

To skip the tests to run 
mvn clean install -DskipTests



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.