Oracle XML Update troubles

Question

I have a BPMN2.0 XML value stored in a CLOB in a table.

it has structure similar to this

<bpmn:definitions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bpmn="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:dc="http://www.omg.org/spec/DD/20100524/DC" xmlns:di="http://www.omg.org/spec/DD/20100524/DI" id="Definitions_17dxovu" targetNamespace="http://bpmn.io/schema/bpmn" exporter="bpmn-js (https://demo.bpmn.io)" exporterVersion="4.0.3">
  <bpmn:collaboration id="Collaboration_1wfeho3">
    <bpmn:participant id="Participant_0fn1t3b" name="Participant" processRef="Process_16me3ie" />
  </bpmn:collaboration>
  <bpmn:process id="Process_16me3ie">
    <bpmn:dataStoreReference id="DataStoreReference_16vk15q" name="Entity Data Repository" />
    <bpmn:startEvent id="Event_0ncce8e">
      <bpmn:outgoing>Flow_16gz7yv</bpmn:outgoing>
    </bpmn:startEvent>
    <bpmn:task id="Activity_12og88i" name="oldval">
    </bpmn:task>

etc.

I desire to simply update the name attribute of the task with a new name.

so I attempted various flavors of this:

UPDATE process p
SET p.bpmn_xml = updatexml( p.bpmn_xml, '//bpmn:task[@id="Activity_12og88i" ]/name[text()="oldval"]/text()', 'newval' );

but I get many errors including this

PL/SQL: ORA-00932: inconsistent datatypes: expected - got - at line xxx

pointers appreciated.

MT0 · Accepted Answer · 2023-03-24T22:57:29.123

You have four issues:

As per this answer, you need to include the namespaces in the 4th argument to UPDATEXML;
UPDATEXML takes an XMLTYPE as the first parameter so you need to convert your CLOB to and from that;
You want to use the XPath //bpmn:task[@id="Activity_12og88i" and @name="oldval"]/@name; and
Your XML is invalid as it is missing closing tags.

Like this:

UPDATE process
SET bpmn_xml = updatexml(
                 XMLTYPE(bpmn_xml),
                 '//bpmn:task[@id="Activity_12og88i" and @name="oldval"]/@name',
                 'newval',
                 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xmlns:bpmn="http://www.omg.org/spec/BPMN/20100524/MODEL"
                  xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI"
                  xmlns:dc="http://www.omg.org/spec/DD/20100524/DC"
                  xmlns:di="http://www.omg.org/spec/DD/20100524/DI"'
               ).getClobVal();

Which, for the sample data:

CREATE TABLE process (bpmn_xml CLOB );

INSERT INTO process (bpmn_xml)
VALUES ('<bpmn:definitions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bpmn="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:dc="http://www.omg.org/spec/DD/20100524/DC" xmlns:di="http://www.omg.org/spec/DD/20100524/DI" id="Definitions_17dxovu" targetNamespace="http://bpmn.io/schema/bpmn" exporter="bpmn-js (https://demo.bpmn.io)" exporterVersion="4.0.3">
  <bpmn:collaboration id="Collaboration_1wfeho3">
    <bpmn:participant id="Participant_0fn1t3b" name="Participant" processRef="Process_16me3ie" />
  </bpmn:collaboration>
  <bpmn:process id="Process_16me3ie">
    <bpmn:dataStoreReference id="DataStoreReference_16vk15q" name="Entity Data Repository" />
    <bpmn:startEvent id="Event_0ncce8e">
      <bpmn:outgoing>Flow_16gz7yv</bpmn:outgoing>
    </bpmn:startEvent>
    <bpmn:task id="Activity_12og88i" name="oldval">
    </bpmn:task>   
  </bpmn:process>
</bpmn:definitions>');

Updates the XML to:

BPMN_XML

<bpmn:definitions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bpmn="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:dc="http://www.omg.org/spec/DD/20100524/DC" xmlns:di="http://www.omg.org/spec/DD/20100524/DI" id="Definitions_17dxovu" targetNamespace="http://bpmn.io/schema/bpmn" exporter="bpmn-js (https://demo.bpmn.io)" exporterVersion="4.0.3"><bpmn:collaboration id="Collaboration_1wfeho3"><bpmn:participant id="Participant_0fn1t3b" name="Participant" processRef="Process_16me3ie"/></bpmn:collaboration><bpmn:process id="Process_16me3ie"><bpmn:dataStoreReference id="DataStoreReference_16vk15q" name="Entity Data Repository"/><bpmn:startEvent id="Event_0ncce8e"><bpmn:outgoing>Flow_16gz7yv</bpmn:outgoing></bpmn:startEvent><bpmn:task id="Activity_12og88i" name="newval">
</bpmn:task></bpmn:process></bpmn:definitions>

fiddle

Oracle XML Update troubles

1 Answers1