Oracle Regex Replace on String Query

Question

Query to fetches the sub xml from an xml saved in the CLOB column of my table is given as :

select REGEXP_REPLACE(xmltype(t.prov_request).extract('//SOAP_Domain_Msg/Body').getStringVal(),'<Body>|</Body>','') xml 
from tbl_prov_comptel

There is "SO1_USERNAME" value="xxx" in the string returned by the above query. What i want to achieve is to form a consolidated query and append something in the start of the expression above.

ie."SO1_USERNAME" value="qwexxx"

Could you provide some sample data and expected output of the data please — Matt, May 09 '16 at 09:30
Append `/*` to the XPath and you can get rid of the `REGEXP_REPLACE` like this: `xmltype(t.prov_request).extract('//SOAP_Domain_Msg/Body/*').getStringVal()` — MT0, May 09 '16 at 09:40
@MT0 Perfect sir. That worked, but thats one part of it. I want to apply replace function on the returned string in the same query. i.e String contains `"SO1_USERNAME" value="xxx"` and I want to add few characters in the string like `"SO1_USERNAME" value="123xxx"` . I have been unable to do so. — jonathan white, May 09 '16 at 09:57
As @Matt said - please provide some sample data and your expected output. There may be a simple way of doing it using XPath (without regular expressions - as [XHTML is not a regular language](http://stackoverflow.com/a/1758162/1509264) so reliably parsing it using regular expressions is not always simple). — MT0, May 09 '16 at 10:00
This is the xml saved in clob. ` ` I intend to write a query which returns me sub xml starting from `` . Also i want to apply the selected xml's username attribute to be appended with some digits ill provide i.e `` @MT0 @Matt — jonathan white, May 09 '16 at 10:12
You do not want to parse that using a regular expression - you can but what happens when the order of the name and value attributes is swapped? It is still valid XML but the regular expression will probably break. Similarly, if there are value attributes in other tags then you may pick up the wrong one. It is better to parse the XML. — MT0, May 09 '16 at 10:44

MT0 · Accepted Answer · 2016-05-09T10:40:37.190

Oracle Setup:

CREATE TABLE tbl_prov_comptl ( prov_request CLOB );

INSERT INTO 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"'
       ) AS SO1_USERNAME,
       x.xml.getStringVal() AS xml
FROM   (
  SELECT XMLType( prov_request ).extract( '//SOAP_Domain_Msg/Body/*' ) AS xml
  FROM   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>

Thank you for the kind help but there is an issue. I want the returning 'XML' to have username appended too. in this case `"SO1_USERNAME" value="ABC222671150"` Please help. — jonathan white, May 09 '16 at 11:08
Just use string concatenation to do that: `xml || '"SO1_USERNAME" value="' || SO1_USERNAME || '"'` — MT0, May 09 '16 at 11:13
I am getting null in `SO1_USERNAME` when i executed the query you provided. — jonathan white, May 09 '16 at 11:24

Oracle Regex Replace on String Query

1 Answers1