Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

replace value inside xml tags

naveenmani912Aug 20 2018 — edited Aug 20 2018

hi

  I have a XML stored in a table like below in a xmltype column

    <pubDat>

      <bNumber>string</bNumberr>

      <fld>string</fld>

      <dbInstance>string</dbInstance>

    </publicationData>

I would like to replace the word 'string' into respective value . Kindly help me out

Comments

cormaco

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

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

cormaco

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.

naveenmani912

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

cormaco

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

naveenmani912

thank you for helping me out

odie_63

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

1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 17 2018
Added on Aug 20 2018
7 comments
14,576 views