A sneeky tool in oracle SQL developer to help u visually generate sql query is here
Printing a tree hierarchy using SQL connect by:
Oracle CONTAINS function for XML based search
select count(*) from ATABLE where contains(xml_COLUMN, 'HASPATH(//filed="12568") ') >0
Extractvalue from xml column
SELECT distinct EXTRACTVALUE( xmltype(column_meta_xml), '//field', 'xmlns:ora="http://xmlns.oracle.com/xdb"') from atable
Printing a tree hierarchy using SQL connect by:
select
lpad('-', (level)*2,' ' ) || HIERARCHY_cat_short_name as padded_name, HIERARCHY_cat_id, HIERARCHY_cat_parent_id, level
from HIERARCHY_category
connect by prior HIERARCHY_cat_id =HIERARCHY_cat_parent_id
start with
HIERARCHY_cat_id in (select HIERARCHY_cat_id from HIERARCHY_category where
HIERARCHY_cat_parent_id is null)
quick reference here :http://philip.greenspun.com/sql/trees.htmllpad('-', (level)*2,' ' ) || HIERARCHY_cat_short_name as padded_name, HIERARCHY_cat_id, HIERARCHY_cat_parent_id, level
from HIERARCHY_category
connect by prior HIERARCHY_cat_id =HIERARCHY_cat_parent_id
start with
HIERARCHY_cat_id in (select HIERARCHY_cat_id from HIERARCHY_category where
HIERARCHY_cat_parent_id is null)
Oracle CONTAINS function for XML based search
select count(*) from ATABLE where contains(xml_COLUMN, 'HASPATH(//filed="12568") ') >0
Extractvalue from xml column
SELECT distinct EXTRACTVALUE( xmltype(column_meta_xml), '//field', 'xmlns:ora="http://xmlns.oracle.com/xdb"') from atable
2 comments:
To update text in an xml node try:
select UPDATEXML(XMLTYPE(column_META_XML), '/image/contributor', 'test')
from TABLE
where column_img_id=38417;
Using regular expression in query :
Query on a clob column to fetch value of a string following 'TERMS :'
select * from
(
select e.id,e.ref_number,
trim(REGEXP_REPLACE( REGEXP_SUBSTR(DBMS_LOB.substr(l.letter_text,4000), 'TERMS[ ]*:.*'), 'TERMS[ ]*:', '' )) letter_terms_field,
DBMS_LOB.substr(l.letter_text,4000) letter_full_text
from event e
join letter l on e.event_letter_id = l.letter_id
)where letter_terms_field is not null
Post a Comment