Home » Developer & Programmer » JDeveloper, Java & XML » how to add some value in already present node in xml type clob column (oracle 10g)
how to add some value in already present node in xml type clob column [message #541379] Tue, 31 January 2012 00:58 Go to next message
kiran111
Messages: 49
Registered: October 2011
Member
hi friends,

need help in adding some value in xml type clob column,
for ex: xml_tab table has more than 100 rows, below i have created some rows

Quote:
create table xml_tab(id number, ex_xml clob);

select * from xml_tab;

1 <CData CI="1111" <Dist> <value desc="25">25 </value> <value desc="30">30 </value> <value desc="35">35 </value> <value desc="40">40 </value> </Dist> <Year IsReadOnly="Y"> <value descr="Old">&lt;1995 </value> <value>1995 </value> <value>1996 </value> <value>1997 </value> <value>1998 </value> <value>1999 </value> <value>2000 </value> <value>2001 </value> <value>2002 </value> <value>2003 </value> <value>2004 </value> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value>2010 </value> </Year> </CData>

2 <CData CI="1110" <Dist> <value desc="24">24 </value> <value desc="33">33 </value> </Dist> </CData>

3 <CData CI="1111" <Dist> <value desc="31">31 </value> </Dist> <Year IsReadOnly="Y"> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value >2010 </value> </Year> </CData>


i want to add 2 new values in <Year>.. </Year> if it is present in the xml
 <value>2011 </value>
 <value>2012 </value>


the output should be:
Quote:

1 <CData CI="1111" <Dist> <value desc="25">25 </value> <value desc="30">30 </value> <value desc="35">35 </value> <value desc="40">40 </value> </Dist> <Year IsReadOnly="Y"> <value descr="Old">&lt;1995 </value> <value>1995 </value> <value>1996 </value> <value>1997 </value> <value>1998 </value> <value>1999 </value> <value>2000 </value> <value>2001 </value> <value>2002 </value> <value>2003 </value> <value>2004 </value> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value>2010 </value> <value>2011 </value>
<value >2012 </value> </Year> </CData>

2 <CData CI="1110" <Dist> <value desc="24">24 </value> <value desc="33">33 </value> </Dist> </CData>

3 <CData CI="1111" <Dist> <value desc="31">31 </value> </Dist> <Year IsReadOnly="Y"> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value>2010 </value> <value>2011 </value> <value>2012 </value> </Year> </CData>


how to do this

[Updated on: Tue, 31 January 2012 04:12] by Moderator

Report message to a moderator

Re: how to add some value in already present node in xml type clob column [message #541385 is a reply to message #541379] Tue, 31 January 2012 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want to add 2 new values in <Year>..</Year> if it is present in the xml

Where? At which level?
Note that your data are not valid XML.

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals.

Keep your lines of code in 80 character width: no more than 80 characters on each line.

Regards
Michel
Re: how to add some value in already present node in xml type clob column [message #541429 is a reply to message #541385] Tue, 31 January 2012 03:56 Go to previous messageGo to next message
kiran111
Messages: 49
Registered: October 2011
Member
Hi Michel,

please find the create and insert statement

create table xml_tab(id number, ex_xml clob);

declare
v_ins1 varchar2(3000):='<note> 
	<to>jeff</to> 
	<from>rai</from> 
	<heading>Reminder</heading> 
	<body>test1</body> 
	<Dist> 
		<value>1</value> 
		<value>2</value> 
		<value>3</value> 
		<value>4</value> 
	</Dist> 
	<Year> 
		<value>1999</value> 
		<value>2000</value> 
		<value>2001</value> 
		<value>2002</value> 
		<value>2003</value> 
		<value>2004</value> 
		<value>2005</value> 
		<value>2006</value> 
		<value>2007</value> 
		<value>2008</value> 
		<value>2009</value> 
		<value>2010</value> 
		</Year> 
  </note>'; 

v_ins2 varchar2(3000):='<note> 
	<to>John</to> 
	<from>Joy</from> 
	<heading>status</heading> 
	<body>test2</body> 
	<Dist> 
		<value>4</value> 
		<value>5</value> 
		<value>35</value> 
		<value>40</value> 
	</Dist> 
  </note>'; 

v_ins3 varchar2(3000):='<note> 
	<to>scott</to> 
	<from>jeffery</from> 
	<heading>status</heading> 
	<body>test3</body> 
	<Year> 
		<value>1995</value> 
		<value>1996</value> 
		<value>1997</value> 
		<value>1998</value> 
		<value>1999</value> 
		<value>2000</value> 
		<value>2001</value> 
		<value>2002</value> 
		<value>2003</value> 
		<value>2004</value> 
		<value>2005</value> 
		<value>2006</value> 
		<value>2007</value> 
		<value>2008</value> 
		<value>2009</value> 
		<value>2010</value> 
	</Year> 
  </note>';
  
begin
insert into xml_tab (id,ex_xml) values(1,v_ins1);
insert into xml_tab (id,ex_xml) values(2,v_ins2);
insert into xml_tab (id,ex_xml) values(3,v_ins3);
end;


what I need is that between <YEAR> and </YEAR>, we have value only till 2010 ( i.e. <value>2010</value> ),
I want to add for 2011 and 2012 (i.e. <value>2011</value>
<value>2012</value> ) , if the <YEAR> node is present.

for example the ex_xml column value for id =3, will be

<note> 
	<to>scott</to> 
	<from>jeffery</from> 
	<heading>status</heading> 
	<body>test3</body> 
	<Year> 
		<value>1995</value> 
		<value>1996</value> 
		<value>1997</value> 
		<value>1998</value> 
		<value>1999</value> 
		<value>2000</value> 
		<value>2001</value> 
		<value>2002</value> 
		<value>2003</value> 
		<value>2004</value> 
		<value>2005</value> 
		<value>2006</value> 
		<value>2007</value> 
		<value>2008</value> 
		<value>2009</value> 
		<value>2010</value> 
		<value>2011</value> 
		<value>2012</value>
 	</Year> 
  </note>


thanks
Re: how to add some value in already present node in xml type clob column [message #541437 is a reply to message #541429] Tue, 31 January 2012 04:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try with INSERTCHILDXML in this case as given below.


SQL> -- Existing XML with Year Node
SQL> with rec as (
  2  SELECT
  3  XMLTYPE('<note>
  4     <to>jeff</to>
  5     <from>rai</from>
  6     <heading>Reminder</heading>
  7     <body>test1</body>
  8     <Dist>
  9             <value>1</value>
 10             <value>2</value>
 11             <value>3</value>
 12             <value>4</value>
 13     </Dist>
 14     <Year>
 15             <value>1999</value>
 16             <value>2000</value>
 17             <value>2001</value>
 18             <value>2002</value>
 19             <value>2003</value>
 20             <value>2004</value>
                <value>2005</value>
 21   22                <value>2006</value>
 23             <value>2007</value>
 24             <value>2008</value>
 25             <value>2009</value>
 26      <value>2010</value>
 27             </Year>
 28    </note>') xml
 29    FROM DUAL)
 30    SELECT *
 31    FROM REC ;

XML
--------------------------------------------------
<note>
  <to>jeff</to>
  <from>rai</from>
  <heading>Reminder</heading>
  <body>test1</body>
  <Dist>
    <value>1</value>
    <value>2</value>
    <value>3</value>
    <value>4</value>
  </Dist>
  <Year>
    <value>1999</value>
    <value>2000</value>
    <value>2001</value>
    <value>2002</value>
    <value>2003</value>
    <value>2004</value>
    <value>2005</value>
    <value>2006</value>
    <value>2007</value>
    <value>2008</value>
    <value>2009</value>
    <value>2010</value>
  </Year>
</note>


SQL>
SQL> -- New Nodes are added to YEAR Node
with rec as (
SQL>   2  SELECT
  3  XMLTYPE('<note>
  4     <to>jeff</to>
  5     <from>rai</from>
  6     <heading>Reminder</heading>
  7     <body>test1</body>
  8     <Dist>
  9             <value>1</value>
 10             <value>2</value>
 11             <value>3</value>
 12             <value>4</value>
 13     </Dist>
 14     <Year>
 15             <value>1999</value>
 16             <value>2000</value>
 17             <value>2001</value>
 18             <value>2002</value>
 19             <value>2003</value>
 20             <value>2004</value>
 21             <value>2005</value>
 22             <value>2006</value>
 23             <value>2007</value>
 24             <value>2008</value>
 25             <value>2009</value>
 26             <value>2010</value>
 27             </Year>
 28    </note>') xml
 29    FROM DUAL)
 30    SELECT INSERTCHILDXML(XML, '/note/Year[value="2010"]','value',
 31               XMLCONCAT (XMLTYPE('<value>2011</value>'),XMLTYPE('<value>2012</value>'))) new_xml
 32    FROM REC;

NEW_XML
--------------------------------------------------
<note>
  <to>jeff</to>
  <from>rai</from>
  <heading>Reminder</heading>
  <body>test1</body>
  <Dist>
    <value>1</value>
    <value>2</value>
    <value>3</value>
    <value>4</value>
  </Dist>
  <Year>
    <value>1999</value>
    <value>2000</value>
    <value>2001</value>
    <value>2002</value>
    <value>2003</value>
    <value>2004</value>
    <value>2005</value>
    <value>2006</value>
    <value>2007</value>
    <value>2008</value>
    <value>2009</value>
    <value>2010</value>
    <value>2011</value>
    <value>2012</value>
  </Year>
</note>


SQL>
SQL> -- Existing XML without Year Node
with rec as (
SQL>   2  SELECT
  3  XMLTYPE('<note>
  4     <to>jeff</to>
  5     <from>rai</from>
  6     <heading>Reminder</heading>
  7     <body>test1</body>
  8     <Dist>
  9             <value>1</value>
 10             <value>2</value>
 11             <value>3</value>
 12             <value>4</value>
 13     </Dist>
 14    </note>') xml
 15    FROM DUAL)
 16    SELECT *
 17    FROM REC ;

XML
--------------------------------------------------
<note>
  <to>jeff</to>
  <from>rai</from>
  <heading>Reminder</heading>
  <body>test1</body>
  <Dist>
    <value>1</value>
    <value>2</value>
    <value>3</value>
    <value>4</value>
  </Dist>
</note>


SQL> -- New Nodes wont be added in this case
with rec as (
SQL>   2  SELECT
  3  XMLTYPE('<note>
  4     <to>jeff</to>
  5     <from>rai</from>
  6     <heading>Reminder</heading>
  7     <body>test1</body>
  8     <Dist>
  9             <value>1</value>
 10             <value>2</value>
 11             <value>3</value>
 12             <value>4</value>
 13     </Dist>
 14    </note>') xml
 15    FROM DUAL)
 16    SELECT INSERTCHILDXML(XML, '/note/Year[value="2010"]','value',
 17               XMLCONCAT (XMLTYPE('<value>2011</value>'),XMLTYPE('<value>2012</value>'))) NEW_XML
 18    FROM REC;

NEW_XML
--------------------------------------------------
<note>
  <to>jeff</to>
  <from>rai</from>
  <heading>Reminder</heading>
  <body>test1</body>
  <Dist>
    <value>1</value>
    <value>2</value>
    <value>3</value>
    <value>4</value>
  </Dist>
</note>


SQL>

[Updated on: Tue, 31 January 2012 05:33]

Report message to a moderator

Re: how to add some value in already present node in xml type clob column [message #541438 is a reply to message #541437] Tue, 31 January 2012 05:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Also you can check specific nodes using EXISTSNODE as given below.


SQL> -- Checks specifically for Node with Value="2010"
with rec as (
SQL>   2  SELECT
  3  XMLTYPE('<note>
  4     <to>jeff</to>
  5     <from>rai</from>
  6     <heading>Reminder</heading>
  7     <body>test1</body>
  8     <Dist>
  9             <value>1</value>
 10             <value>2</value>
 11             <value>3</value>
 12             <value>4</value>
 13     </Dist>
 14     <Year>
 15             <value>1999</value>
 16             <value>2000</value>
 17             <value>2001</value>
 18             <value>2002</value>
 19             <value>2003</value>
 20             <value>2004</value>
 21             <value>2005</value>
 22             <value>2006</value>
 23             <value>2007</value>
 24             <value>2008</value>
 25             <value>2009</value>
 26             <value>2010</value>
 27             </Year>
 28    </note>') xml
 29    FROM DUAL)
 30    SELECT *
 31    FROM REC
 32    WHERE EXISTSNODE (xml,'//Year[value="2010"]') =1;

XML
--------------------------------------------------
<note>
  <to>jeff</to>
  <from>rai</from>
  <heading>Reminder</heading>
  <body>test1</body>
  <Dist>
    <value>1</value>
    <value>2</value>
    <value>3</value>
    <value>4</value>
  </Dist>
  <Year>
    <value>1999</value>
    <value>2000</value>
    <value>2001</value>
    <value>2002</value>
    <value>2003</value>
    <value>2004</value>
    <value>2005</value>
    <value>2006</value>
    <value>2007</value>
    <value>2008</value>
    <value>2009</value>
    <value>2010</value>
  </Year>
</note>


SQL>
Re: how to add some value in already present node in xml type clob column [message #541874 is a reply to message #541438] Thu, 02 February 2012 23:29 Go to previous message
kiran111
Messages: 49
Registered: October 2011
Member
Hi rajavu1,

Thanks for your help.
I did something as below to update the column.

update xml_tab 
set ex_xml= replace(xmltype(ex_xml),'<value>2010</value>',
'<value>2010</value><value>2011</value><value>2012</value>')
where instr(xmltype(ex_xml),'<value>2010</value>') > 0


thanks again

[Updated on: Fri, 03 February 2012 01:17] by Moderator

Report message to a moderator

Previous Topic: get specs from html
Next Topic: Matrix Report Creation
Goto Forum:
  


Current Time: Sun May 24 18:49:01 CDT 2020