replace value inside xml tags

Your xml example is invalid, for the future please make sure to test your examples before posting them.

Here is how to replace a value in a node:

WITH testdata(xmlfile) AS (SELECT xmltype('<pubDat>      <bNumber>string</bNumber>      <fld>string</fld>      <dbInstance>string</dbInstance></pubDat>') FROM dual)SELECT XMLQUERY(    'copy $tmp := .     modify replace value of node $tmp/pubDat/bNumber with "test"     return $tmp    'PASSING xmlfile RETURNING CONTENT)FROM testdata

Output:

<pubDat>  <bNumber>test</bNumber>  <fld>string</fld>  <dbInstance>string</dbInstance></pubDat>                    

naveenmani912 schrieb:hi cormaco thank you for quick response, i am using oracle 10g would this query work in 10g version. I executed the query its showing some errors

In that case use this command:

SELECT UPDATEXML(xmlfile,'/pubDat/bNumber/text()','test') FROM testdata

This command is deprecated in later versions of Oracle and the xquery update facility from my first response should be used instead.

That would be

SELECT DELETEXML(xmlfile,'/pubDat/fld') FROM testdata

You can find the documentation of all Oracle xmlfunctions here:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#i1032611

Please mark the thread as answered if you're OK with it.