sql - Oracle Regex Replace on String Query -

-

query fetches sub xml xml saved in clob column of table given :

select regexp_replace(xmltype(t.prov_request).extract('//soap_domain_msg/body').getstringval(),'<body>|</body>','') xml  tbl_prov_comptel

there "so1_username" value="xxx" in string returned above query. want achieve form consolidated query , append in start of expression above.

ie."so1_username" value="qwexxx"

oracle setup:

create table tbl_prov_comptl ( prov_request clob );  insert tbl_prov_comptl values (   '<soap_domain_msg><body><ns4:modifyrequest xmlns:ns4="http://soa.comptel.com/2011/02/instantlink"><ns4:requestparameters> <ns4:parameter name="so1_username" value="222671150"/></ns4:requestparameters> </ns4:modifyrequest></body></soap_domain_msg>' );

query:

select extractvalue(          xml,          '//ns4:modifyrequest/ns4:requestparameters/ns4:parameter[name="so1_username"]/@value',         'xmlns:ns4="http://soa.comptel.com/2011/02/instantlink"'        ) so1_username,        x.xml.getstringval() xml   (   select xmltype( prov_request ).extract( '//soap_domain_msg/body/*' ) xml     tbl_prov_comptl ) x;

output:

so1_username xml ------------ ------------------------------------------------------------------------------ 222671150    <ns4:modifyrequest xmlns:ns4="http://soa.comptel.com/2011/02/instantlink"><ns4              :requestparameters> <ns4:parameter name="so1_username" value="222671150"/></ns              4:requestparameters> </ns4:modifyrequest>

Comments

Post a Comment