Hi,
I have a long XML, which is of the sort:
<something>
<root>
<tag id="1"> ... </tag>
<tag id="2"> ... </tag>
<tag id="3" myattr="3"> ... </tag>
<tag id="4"> ... </tag>
<tag id="5" myattr="4"> ... </tag>
</root>
</something>
So, from that I need to save the XML and also generate something in DB.
The IDs in TAG will not be the sames as the IDs created in DB, but I will need the IDs on DB to be added into the XML to have a reference on whether something changes, or is added or deleted.
So, I would need to:
1- Make sure that in the XML any "tag" that does not have the myattr, to have it added.
2- Change the myattr to the corresponding DB value, when Tag@id = "nn".
I have been searching and I found something that would let me to update the value dynamically:
with xd as (
select xml_data_field
from xml_data_table
where xml_id = 3117
)
select
xmlquery(
'copy $tmp := .
modify (for $i in $tmp//*[local-name()=$input_tag]/@*[local-name()=$input_attribute]
return replace value of node $i with $input_value)
return $tmp'
PASSING xmltype(xd.xml_data_field), 'something/root/tag' AS "input_tag", 'myattr' AS "input_attribute", '1' AS "input_value"
returning content
).GetClobVal()
from xd
;
what I would be missing here is the table with the DB IDs and then the "relationship" to supply the '1' (for the input_value) as the id from db, but also I would be missing, which I am not sure if it needs to be done in 2 steps, or could be done in one... how to update those nodes (/tag) in which the attribute (myattr) already exists, and insert in those nodes (/tag) in which the attribute (myattr) does not exist.
Even if I had to do it in 2 steps:
1- Add the attribute with a "" value in those nodes (/tag) in which the attribute (myattr) does not exist
2- Replace the value for specific node "id = nn"
I hope I am clear on what I need, I have been searching but I could not find anything that worked for the first step (except insertchildxml, but it is being deprecated in Oracle 12 and I do not want to use that, I would rather use xmlquery)... For the 2nd step I have a vague idea on how to search for a node with specific id, but at this point would be helpful if someone gave me an example.
Thanks and regards,
Jorgelina