[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4402
  • Last Modified:

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.


0
jnajayjain
Asked:
jnajayjain
  • 5
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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()
0
 
jnajayjainAuthor Commented:
Hi slightwv,
I regret.

The output of your query is coming as below -  Which is not helpful.
--------------------------------------------------------
<document>
  <document-metadata/>
  <feature content-id="14850">&lt;feature content-id=&quot;14850&quot; acl=”500900907,500900906” &gt; This is a feature Content &lt;/feature&gt;</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.
0
 
slightwv (䄆 Netminder) Commented:
Sorry about that.

I think the issue is with the characters around the acl attribute.  They don't appear to be double-quotes.  

Give this a try:
SELECT UPDATEXML(XMLTYPE(xml_file),'/document/feature', '<feature content-id="14850" acl="”500900907,500900906”" > This is a feature Content </feature>').getstringval()
FROM tab1
/
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sujit_kumarCommented:
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>');


0
 
jnajayjainAuthor Commented:
Yep It is working well now.

Thanks,
Ajay.
0
 
jnajayjainAuthor Commented:
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.


0
 
jnajayjainAuthor Commented:
CAN ANYONE PLEASE GIVE ME A QUICK HELP ON THIS ?

Thanks,
Ajay.
0
 
jnajayjainAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
Glad you found the answer.  Sorry I could note get back to you on this.  I was out of the office on Friday.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now