Update XML Clob node

Question

I have a table Transaction , in which I have a clob xclob for which ,

I want to update the "property" node's "record_start_dll_date" value to record_start_date(i.e I want to remove the _dll part ) and "record_dll_end_date" to record_end_date . I am using oracle database . How can I modify these node values??

<?xml version ="1.0"?>
<properties>
   <property name ="record_start_dll_date">
   <value>1/1/2021</value>
   </property>
    <property name ="record_dll_end_date">
   <value>21/12/2021</value>
   </property>
</properties>

Answer 1

You can use an XMLQuery update, either with the specific attribute values:

xmlquery ('copy $i := $p1 modify (
  (for $j in $i/properties/property[@name="record_start_dll_date"]/@name
    return replace value of node $j with $p2),
  (for $j in $i/properties/property[@name="record_dll_end_date"]/@name
    return replace value of node $j with $p3)
  ) return $i'
  passing xmltype(xclob) as "p1",
    'record_start_date' as "p2",
    'record_end_date' as "p3"
  returning content)

or to strip any _dll:

xmlquery ('copy $i := $p1 modify (
  for $j in $i/properties/property[contains(@name, "_dll")]/@name
    return replace value of node $j with replace($j, "_dll", "")
  ) return $i'
  passing xmltype(xclob) as "p1"
  returning content)

Either way you can incorporate that into an update statement, with a matching XMLExists clause to avoid unnecessary updates:

update transaction
set xclob = xmlquery ('copy $i := $p1 modify (
  for $j in $i/properties/property[contains(@name, "_dll")]/@name
    return replace value of node $j with replace($j, "_dll", "")
  ) return $i'
  passing xmltype(xclob) as "p1"
  returning content).getclobval()
where xmlexists('$p1/properties/property[contains(@name, "_dll")]'
  passing xmltype(xclob) as "p1")

db<>fiddle