Home » Developer & Programmer » JDeveloper, Java & XML » update the node value (oracle 10.2.0.1)
update the node value [message #479793] Tue, 19 October 2010 06:06 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I have the below xml as a record in oracle table. I want to update the "job" (exists in REF_MAKE_USER Node) value as 0.

<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <REFGROUP_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>0</VERS_MINOR>
      <REFNAME>REFRESH_TESTFL_5MINS</REFNAME>
      <OWNER_NUM>68</OWNER_NUM>
      <REFOWNER>REPADMIN</REFOWNER>
      <REFGROUP>241</REFGROUP>
      <REF_MAKE_USER>[Edit MC: Remove useless long data for just "Something"]</REF_MAKE_USER>
    </REFGROUP_T>
  </ROW>
</ROWSET>


Is it possible to update the part of value in node?

If yes, please let me know.

Thanks & Regards,
Madhavi.

[Updated on: Tue, 19 October 2010 06:25] by Moderator

Report message to a moderator

Re: update the node value [message #479797 is a reply to message #479793] Tue, 19 October 2010 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 character width.

SQL> with data as (
  2    select xmltype('
  3  <ROWSET>
  4    <ROW>
  5      <REFGROUP_T>
  6        <VERS_MAJOR>1</VERS_MAJOR>
  7        <VERS_MINOR>0</VERS_MINOR>
  8        <REFNAME>REFRESH_TESTFL_5MINS</REFNAME>
  9        <OWNER_NUM>68</OWNER_NUM>
 10        <REFOWNER>REPADMIN</REFOWNER>
 11        <REFGROUP>241</REFGROUP>
 12        <REF_MAKE_USER>Something</REF_MAKE_USER>
 13      </REFGROUP_T>
 14    </ROW>
 15  </ROWSET>') val from dual
 16    )
 17  select updatexml(val,'//REF_MAKE_USER/text()','### MY JOB ###') val
 18  from data
 19  /
VAL
--------------------------------------------------------------------------------
<ROWSET><ROW><REFGROUP_T><VERS_MAJOR>1</VERS_MAJOR><VERS_MINOR>0</VERS_MINOR><RE
FNAME>REFRESH_TESTFL_5MINS</REFNAME><OWNER_NUM>68</OWNER_NUM><REFOWNER>REPADMIN<
/REFOWNER><REFGROUP>241</REFGROUP><REF_MAKE_USER>### MY JOB ###</REF_MAKE_USER><
/REFGROUP_T></ROW></ROWSET>

Regards
Michel

[Updated on: Tue, 19 October 2010 06:24]

Report message to a moderator

Re: update the node value [message #480112 is a reply to message #479797] Thu, 21 October 2010 04:33 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Thank you Michel for response.

But i need to keep total long data in the node (REF_MAKE_USER). Thats the metadata of refresh group. I want just update the JOB value as 0 in the node. If i remove total data in node, this xml data is not useful to create a refresh group in another site.

<ROWSET>
  <ROW>
    <REFGROUP_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>0</VERS_MINOR>
      <REFNAME>REFRESH_TESTFL_5MINS</REFNAME>
      <OWNER_NUM>68</OWNER_NUM>
      <REFOWNER>REPADMIN</REFOWNER>
      <REFGROUP>241</REFGROUP>
      <REF_MAKE_USER>dbms_refresh.make(&apos;&quot;REPADMIN&quot;.&quot;REFRESH_TESTFL_5MINS&quot;&apos;,list=&gt;null,next_date=&gt;null,interval=&gt;null,implicit_destroy=&gt;FALSE,lax=&gt;FALSE,job=&gt;462,rollback_seg=&gt;NULL,push_deferred_rpc=&gt;TRUE,refresh_after_errors=&gt;TRUE,purge_option =&gt; 1,parallelism =&gt; 0,heap_size =&gt; 0);</REF_MAKE_USER>
    </REFGROUP_T>
  </ROW>
</ROWSET>


I'm expecting to update only JOB value in the node.
Is it possible? If yes, please let me know the function/method.

Thanks & Regards,
Madhavi.
Re: update the node value [message #480122 is a reply to message #480112] Thu, 21 October 2010 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want just update the JOB value as 0 in the node.

It should be worth you posted this in the original question, don't you think?
As soon as I have a moment I'll have a look at this.

Regards
Michel
Re: update the node value [message #480143 is a reply to message #480122] Thu, 21 October 2010 07:47 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I'm achieving this using SUBSTR and INSTR functions by extracting (using EXTRACT function) the node value.
Regards,
Madhavi.
Re: update the node value [message #480153 is a reply to message #480143] Thu, 21 October 2010 08:24 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's a way, could you post it here for future readers.

Regards
Michel
Previous Topic: JDeveloper - two problems
Next Topic: Problem with XMLTABLE with parameters
Goto Forum:
  


Current Time: Thu Mar 28 15:39:58 CDT 2024