1 Reply Latest reply: Sep 26, 2013 5:30 AM by odie_63 RSS

    Update xml string values.

    user621309

      Hi,

      I'm on 11.2.0.2 and got table with nclob column which stores long xml string .

      {code}

      "<?xml version="1.0" encoding="UTF-8"?>

      <?fuego version="6.5.2" build="101272"?>

      <set>

      <configuration name="TEST Database" type="SQL" subtype="DDORACLE">

        <property name="jdbc.pool.idle_timeout" value="5"/>

        <property name="jdbc.pool.entry.max" value="10"/>

        <property name="oracle.dateEqualsTimestamp" value="false"/>

        <property name="jdbc.schema" value="user1"/>

        <property name="jdbc.host" value="hostname"/>

        <property name="user" value="user1"/>

        <property name="jdbc.port" value="1521"/>

        <property name="jdbc.pool.min" value="0"/>

        <property name="jdbc.pool.maxopencursors" value="50"/>

        <property name="oracle.sid" value="dbsid"/>

        <property name="password" value="user101"/>

        <property name="jdbc.xa" value="false"/>

        <property name="jdbc.pool.max" value="10"/>

      </configuration>

      <configuration name="TEST Database2" type="SQL" subtype="DDORACLE">

        <property name="jdbc.pool.idle_timeout" value="5"/>

        <property name="jdbc.pool.entry.max" value="10"/>

        <property name="oracle.dateEqualsTimestamp" value="false"/>

        <property name="jdbc.schema" value="user2"/>

        <property name="jdbc.host" value="hostname"/>

        <property name="user" value="user2"/>

        <property name="jdbc.port" value="1521"/>

        <property name="jdbc.pool.min" value="0"/>

        <property name="jdbc.pool.maxopencursors" value="50"/>

        <property name="oracle.sid" value="dbsid2"/>

        <property name="password" value="user201"/>

        <property name="jdbc.xa" value="false"/>

        <property name="jdbc.pool.max" value="10"/>

      </configuration>

      </set>

      "

      {code}

       

      My goal is to update password value in such way that it equals to value from jdbc.schema value  <property name="jdbc.schema" value="user2"/>   so in this case user2 || '01'

      <property name="password" value="user201"/>   <-- that is my goal .

       

      Regards

      Greg

        • 1. Re: Update xml string values.
          odie_63

          Hi,

           

          You can find a few methods here : How To : Update XML nodes with values from the same document | Odie's Oracle Blog

          They're not all applicable to your version and settings though.

           

          Here's the first one applied to your case :

          declare

           

            v_xmldoc   xmltype;

           

          begin

           

            select xmlparse(document to_clob(t.xmldoc))

            into v_xmldoc

            from my_nclob_table t

            where t.id = 1;

           

            for r in (

              select idx, schema_name

              from my_nclob_table t

                 , xmltable(

                     '/set/configuration'

                     passing v_xmldoc

                     columns idx         for ordinality

                           , schema_name varchar2(30) path 'property[@name="jdbc.schema"]/@value'

                   )

            )

            loop

           

              select updatexml(

                       v_xmldoc

                     , '/set/configuration['||to_char(r.idx)||']/property[@name="password"]/@value'

                     , r.schema_name || '01'

                     )

              into v_xmldoc

              from dual ;

           

            end loop;

           

            update my_nclob_table t

            set t.xmldoc = to_nclob(xmlserialize(document v_xmldoc indent))

            where t.id = 1;

           

          end;

          /

           

          Here's another one, using DOM :

          declare

           

            doc   clob;

           

            p        dbms_xmlparser.Parser;

            domdoc   dbms_xmldom.DOMDocument;

            docnode  dbms_xmldom.DOMNode;

           

            conf_list      dbms_xmldom.DOMNodeList;

            conf_node      dbms_xmldom.DOMNode;

            password_node  dbms_xmldom.DOMNode;

           

            schema_name     varchar2(30);

            password_value  varchar2(256);

           

          begin

           

            select to_clob(xmldoc)

            into doc

            from my_nclob_table

            where id = 1 ;

           

            p := dbms_xmlparser.newParser;

            dbms_xmlparser.parseClob(p, doc);

            domdoc := dbms_xmlparser.getDocument(p);

            dbms_xmlparser.freeParser(p);

           

            docnode := dbms_xmldom.makeNode(domdoc);

            conf_list := dbms_xslprocessor.selectNodes(docnode, '/set/configuration');

           

            for i in 0 .. dbms_xmldom.getLength(conf_list) - 1 loop

            

              conf_node := dbms_xmldom.item(conf_list, i);

              dbms_xslprocessor.valueOf(conf_node, 'property[@name="jdbc.schema"]/@value', schema_name);

              password_node := dbms_xslprocessor.selectSingleNode(conf_node, 'property[@name="password"]/@value');

              dbms_xmldom.setNodeValue(password_node, schema_name || '01');

             

            end loop;

           

            dbms_xmldom.writeToClob(domdoc, doc);

            dbms_xmldom.freeDocument(domdoc);

           

            update my_nclob_table t

            set t.xmldoc = to_nclob(doc)

            where t.id = 1;

           

          end;

          /

           

          Message was edited by: odie_63 - added DOM example