This discussion is archived
1 Reply Latest reply: Sep 26, 2013 3:30 AM by odie_63 RSS

Update xml string values.

user621309 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points