asked on

updateXML function of Oracle XML DB - how to replace a node in xml stored in CLOB column of database.

Guys,

How can I replace a node (with some another) in xml which is stored in the CLOB column of a table.
I want to use the updateXML function such that, After updating the xml, it should return a new instance of XMLType.

heres the complete scenario.

table is -
create table TAB1
(
CONTENT_ID NUMBER(9),
XML_FILE CLOB
)

select * from tab1
CONTENT_ID XML_FILE
15000 <CLOB>

The XML_FILE column contains the below XML.
<document>
<document-metadata/>
<feature content-id="14850">asda</feature>
<content-group content-id="14845"/>
<content-group>Content is within the document itself</content-group>
<content-group content-id="14847"/>
<content-group>Content is within the document itself</content-group>
</document>

Now I want to replace 3rd line of xml (below)
<feature content-id="14850">asda</feature>
With
<feature content-id="14850" acl=”500900907,500900906” > This is a feature Content </feature>
So that I will get a new instance of XML look like -
<document>
<document-metadata/>
<feature content-id="14850" acl=”500900907,500900906” > This is a feature Content </feature>
<content-group content-id="14845"/>
<content-group>Content is within the document itself</content-group>
<content-group content-id="14847"/>
<content-group>Content is within the document itself</content-group>
</document>

I am trying the below query but it is not working.

SELECT UPDATEXML(XMLTYPE(xml_file),'/document/feature', '<feature content-id="14850" acl=”500900907,500900906” > This is a feature Content </feature>').getstringval()
FROM tab1

thanks in advance.
Ajay.

slightwv (䄆 Netminder)

You were real close.

See if this is it (notice the addition of "text()"):
SELECT UPDATEXML(XMLTYPE(xml_file),'/document/feature/text()', '<feature content-id="14850" acl=”500900907,500900906” > This is a feature Content </feature>').getstringval()

jnajayjain

ASKER

Hi slightwv,
I regret.

The output of your query is coming as below - Which is not helpful.
--------------------------------------------------------
<document>
<document-metadata/>
<feature content-id="14850"><feature content-id="14850" acl=”500900907,500900906” > This is a feature Content </feature></feature>
<content-group content-id="14845"/>
<content-group>Content is within the document itself</content-group>
<content-group content-id="14847"/>
<content-group>Content is within the document itself</content-group>
</document>
-------------------------------------------------------

Thanks,
Ajay.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sujit_kumar

You can do this by,

update TAB1 set XML_FILE = sys.XMLTYPE.UPDATEXML(XML_FILE, '/document/feature', '<feature content-id="14850" acl=”500900907,500900906” > This is a feature Content </feature>');

jnajayjain

ASKER

Yep It is working well now.

Thanks,
Ajay.

jnajayjain

ASKER

Hi slightwv ,

Points for this is yours but could you pelase do a favour -
Can I store the below tag in a varchar2 field say

tempFeature varchar2(1000) = '<feature content-id="14850" acl=”500900907,500900906” > This is a feature Content </feature>' ;

and then use the updateXML such taht -
SELECT UPDATEXML(XMLTYPE(xml_file),'/document/feature', 'valueOf(tempFeature)').getstringval()
FROM tab1

where valueOF(tempfeature) replaces the string stored in it.

Thanks,
Ajay.

jnajayjain

ASKER

CAN ANYONE PLEASE GIVE ME A QUICK HELP ON THIS ?

Thanks,
Ajay.

jnajayjain

ASKER

Hey it was a silly question which I asked. answer is simple as below.

SELECT UPDATEXML(XMLTYPE(xml_file),'/document/feature', tempFeature).getstringval()
FROM tab1

Thanks,
Ajay.

slightwv (䄆 Netminder)

Glad you found the answer. Sorry I could note get back to you on this. I was out of the office on Friday.