how to add some value in already present node in xml type clob column [message #541379] |
Tue, 31 January 2012 00:58 |
|
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"><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"><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 #541429 is a reply to message #541385] |
Tue, 31 January 2012 03:56 |
|
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 |
|
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
|
|
|
|
|