Home » Developer & Programmer » JDeveloper, Java & XML » updating an element text in XML document (oracle 11g)
updating an element text in XML document [message #570785] Thu, 15 November 2012 15:33 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Hi,

I have table called shipper with document_xml as CLOB column

The sample content of document_xml is below

<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/pushpin.png</href></Icon>

and there are other attributes continued



Objective: I want to replace "href" element in each of the record with "http://http://localhost:63077/Icons/Markers/ship.png" value.

After I update the row the data should look as follows

<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/ship.png</href></Icon>.


I have to do this for bunch of records in a table(table_name = shipper).

Is there an oracle function to replace/update the href element in a CLOB column?

Any help is greatly appreciated.


Thanks


Re: updating an element text in XML document [message #570786 is a reply to message #570785] Thu, 15 November 2012 15:41 Go to previous messageGo to next message
BlackSwan
Messages: 23161
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Objective: I want to replace "href" element in each of the record with "http://http://localhost:63077/Icons/Markers/ship.png" value.

above does ("http://http://localhost") not match below ("http://localhost"), so I am confused.

>After I update the row the data should look as follows
><ICON><href>http://localhost:63077/Icons/Markers/ship.png</href></Icon>.

Why did you NOT provide a functioning Test Case?
Re: updating an element text in XML document [message #570787 is a reply to message #570786] Thu, 15 November 2012 16:57 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Sorry for the typo but basically I want to replace the href element in an xml document.

What ever may be the content between <href> <href> tags I need to replace with a one value ex:http://localhost:63077/Icons/Markers/ship.png".

Thanks
Re: updating an element text in XML document [message #570788 is a reply to message #570787] Thu, 15 November 2012 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 23161
Registered: January 2009
Senior Member
You choose to ignore Posting Guidelines.
I choose to ignore your threads.
Re: updating an element text in XML document [message #570828 is a reply to message #570785] Fri, 16 November 2012 03:18 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After fixing your data to be a valid XML string:
SQL> with 
  2    data as (
  3      select xmltype('<placemark id = "5467">
  4  <name> 415-CI-78492</name>
  5  <description><![CDATA[<//!!!!//15249599]]></description>
  6  <ICON><href>http://localhost:63077/Icons/Markers/pushpin.png</href></ICON>
  7  </placemark>') val from dual
  8    )
  9  select updatexml(val,'//href/text()','http://http://localhost:63077/Icons/Markers/ship.png')
 10  from data
 11  /
UPDATEXML(VAL,'//HREF/TEXT()','HTTP://HTTP://LOCALHOST:63077/ICONS/MARKERS/SHIP.
--------------------------------------------------------------------------------
<placemark id="5467"><name> 415-CI-78492</name><description><![CDATA[<//!!!!//15
249599]]></description><ICON><href>http://http://localhost:63077/Icons/Markers/s
hip.png</href></ICON></placemark>

Regards
Michel
Previous Topic: Characterset issue in XML (merged)
Next Topic: Exception when try to navigate out of region
Goto Forum:
  


Current Time: Mon Dec 22 12:50:10 CST 2014

Total time taken to generate the page: 0.13617 seconds