Hi Everyone,
I have a CLOB column in my table that carries an XML date. Wherein I have requirement to update the child attributes when they repeats with the same parents several times in the data.
For example:
Say my XML data as follows,
<?xml version="1.0" ?>
<sample xmlns="https://www.abc.com">
<a><b>First detail line</b>
</c>Second detail line</c>
</a>
<d><c>Paris</c></d>
<d><c>France</c></d>
<d><c>Japan</c></d>
</sample>
I want it to update as
<?xml version="1.0" ?>
<sample xmlns=''https://www.abc.com">
<a><b>First detail line</b>
</c>Second detail line</c>
</a>
<d><c1>Paris</c1></d>
<d><c2>France</c2></d>
<d><c3>Japan</c3></d>
</sample>
I tried identifying the repetition of my parent attribute using the below query and tried updating their attribute by extracting XML of each parent attribute in a loop. But that doesn't work
select regexp_count(xml_val,'<d>') from xml_load where xml_name='sample1'; --This gives me more than 10K as count
Identified the parent attributes by using ,
select
count(1)
from xml_load x
where existsNode(extract(xmltype(xml_val),'/*[local-name()="sample"]'),
'/d') =1 --This never returned me 1. I couldnt figure out the reason as well though an attribute called <d> exists.
Please provide your help in achieving my requirement. Any help would be greatly appreciated.