DevOps, CI/CD and Automation

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.

hi cormaco

your suggestion has worked thank you . I have one more query. lets say when i want to remove a node from xml, how can i do that using oracle xml utlilty.

1. <pubDat> 
2.   <bNumber>test</bNumber> 
3.   <fld>string</fld> 
4.   <dbInstance>string</dbInstance> 
5. </pubDat>

To remove fId tag like below

1. <pubDat> 
2.   <bNumber>test</bNumber> 
3.   <dbInstance>string</dbInstance> 
4. </pubDat>

kindly, help me please

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

thank you for helping me out

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