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

Post a Comment

Popular posts from this blog

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)? str: compressedString; return output; } }
Post a Comment
Read more

Self Signed Certificates Vs Signed Certificates (CA Certificates)

Certificates Certificates basically two categories. Self Signed Certificates  - will create by self CA Certificates  - will be  provided by Third party vendor with robust algorithms Depends on the location of installing the certificate these are two types 1. Public Key Certificates (Client Side) 2. Private Key Certificates (Server Side) Self Signed Certificates   If any one is using self signed certificates in their applications they have to make sure both server side and client side certificates are in sync. Other wise we should be ready to face SSLHandShake Exceptions. These will be preferable mostly for lower environments not for production. CA certificates  If you install CA certificates on server side, client side certificates are installed automatically whenever they access the server. So in production for CA certificates there is no need to install the client side certificates. We can generate a Self Signed Certificate using Java Key tool JAVA_HOME/bi...
1 comment
Read more

String Pool Vs String Definition (Using 'new' Operator)

Strings can be defined in Java in two ways. 1. String Literals 2  Traditional way(using 'new' operator) String Literals If a String is created by using String literal notation, memory will be allocated directly in string pool. String pool is subset of Heap memory (Where objects will be created). e.g. String companyName = "Surimenus";      String empName= "Bhargav";       Using 'New' Operator If a String is created using new operator, memory will be allocated in Heap Memory not in String pool. e.g. String companyName = new String("Surimenus"); String empName= new String("Bhargav"); String Pool vs Using 'new' Operator String which are created in String pool will re-reference by reference which contains the same content. For example consider the following. String cn1 = "Surimenu"; String cn2 = "Surimenu"; In the above scenario cn1 and cn2 references having the same content and these are created in Strin...
Post a Comment
Read more