Forum Stats

  • 3,838,585 Users
  • 2,262,383 Discussions
  • 7,900,690 Comments

Discussions

replace value inside xml tags

naveenmani912
naveenmani912 Member Posts: 43 Red Ribbon
edited Aug 20, 2018 7:49AM in PL/SQL XML Programming

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

Tagged:

Answers

  • cormaco
    cormaco Member Posts: 1,952 Silver Crown
    edited Aug 20, 2018 3:38AM

    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
    naveenmani912 Member Posts: 43 Red Ribbon
    edited Aug 20, 2018 5:11AM

    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
    cormaco Member Posts: 1,952 Silver Crown
    edited Aug 20, 2018 5:25AM
    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
  • naveenmani912
    naveenmani912 Member Posts: 43 Red Ribbon
    edited Aug 20, 2018 5:54AM

    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

    naveenmani912
  • cormaco
    cormaco Member Posts: 1,952 Silver Crown
    edited Aug 20, 2018 6:09AM

    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
  • naveenmani912
    naveenmani912 Member Posts: 43 Red Ribbon
    edited Aug 20, 2018 6:12AM

    thank you for helping me out

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Aug 20, 2018 7:49AM

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

This discussion has been closed.