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.




Compress a String

package com.sbs.java8.praticse;

public class StringCompression {

public StringCompression() {
// TODO Auto-generated constructor stub
}

public static void main(String[] args) {
System.out.println(compressString("aaaabbbbbccccAAAAccccccccdefg"));
}

public static String compressString(String str) {
//String str = "aaaabbbbbcccc";
char[] charArray = str.toCharArray();
String compressedString = "";
int i = 0;
while (i < charArray.length) { int counter = 1; int j = i + 1; while (j < charArray.length && charArray[i] == charArray[j]) { counter++; j++; i++; } compressedString = compressedString + charArray[i] + counter; i++; } //System.out.println(compressedString); String output =(compressedString.length() > charArray.length)? str: compressedString;
return output;
}
}