How to select part of the CLOB column with SQL

I am trying to get part of data from CLOB column which contain XML.

I need this because I want to create a report based on this data.

Table structure:

DIAGRAM ( ID number, XML clob );

XML example:

<bpmn:task id="Task_3" name="Identify Application">
        <bpmn:documentation id="Task_3_Documentation">lorem ipsum 1</bpmn:documentation>
        <bpmn:incoming>SequenceFlow_4</bpmn:incoming>
        <bpmn:outgoing>SequenceFlow_3</bpmn:outgoing>
        <bpmn:ioSpecification>
            <bpmn:dataInput />
            <bpmn:dataOutput />
            <bpmn:inputSet>
                <bpmn:dataInputRefs>DataObject</bpmn:dataInputRefs>
            </bpmn:inputSet>
            <bpmn:outputSet>
                <bpmn:dataOutputRefs>DataObject_2</bpmn:dataOutputRefs>
            </bpmn:outputSet>
        </bpmn:ioSpecification>
        <bpmn:property id="Property_2" name="__targetRefPlaceholder" />
        <bpmn:property id="Property_0nnkfoz" name="__targetRef_placeholder" />
        <bpmn:dataInputAssociation id="DataAssociation_4-input">
            <bpmn:sourceRef>DataObject</bpmn:sourceRef>
            <bpmn:targetRef>Property_2</bpmn:targetRef>
        </bpmn:dataInputAssociation>
        <bpmn:dataInputAssociation id="DataInputAssociation_0ur4zio">
            <bpmn:sourceRef>DataObjectReference_0qo1qcm</bpmn:sourceRef>
            <bpmn:targetRef>Property_0nnkfoz</bpmn:targetRef>
        </bpmn:dataInputAssociation>
        <bpmn:dataOutputAssociation id="DataAssociation_3-output">
            <bpmn:targetRef>DataObject_2</bpmn:targetRef>
        </bpmn:dataOutputAssociation>
        <bpmn:dataOutputAssociation id="DataOutputAssociation_1rae1k7">
            <bpmn:targetRef>DataObjectReference_0v1uvlu</bpmn:targetRef>
        </bpmn:dataOutputAssociation>
    </bpmn:task>

--- some XML here

<bpmn:task id="Task_2" name="Define Schedule">
            <bpmn:documentation id="Task_2_Documentation">lorem ipsum 2</bpmn:documentation>
            <bpmn:incoming>SequenceFlow</bpmn:incoming>
            <bpmn:outgoing>SequenceFlow_2</bpmn:outgoing>
            <bpmn:ioSpecification>
                <bpmn:dataOutput />
                <bpmn:inputSet />
                <bpmn:outputSet>
                    <bpmn:dataOutputRefs>DataObject_4</bpmn:dataOutputRefs>
                </bpmn:outputSet>
            </bpmn:ioSpecification>
            <bpmn:dataOutputAssociation id="DataAssociation_5-output">
                <bpmn:targetRef>DataObject_4</bpmn:targetRef>
            </bpmn:dataOutputAssociation>
            <bpmn:dataOutputAssociation id="DataOutputAssociation_1cvojts">
                <bpmn:targetRef>DataObjectReference_1t3shp5</bpmn:targetRef>
            </bpmn:dataOutputAssociation>
        </bpmn:task>

I want get this results:

TASK    NAME                  DOCUMENTATION
Task_3  Identify Application  lorem ipsum 1
Task_2  Define Schedule       lorem ipsum 2

Thanks in advance!

Answer

The main problem is that your XML is invalid. It’s missing the <?xml..> line, and it doesn’t have a root element, much less declare the namespaces. You’ll need to add those if you want to use Oracle’s XML functions.

But if you fix your XML, it’s not too bad.

-- set up the example data
with DIAGRAM as (select to_clob('<?xml version="1.0" encoding="utf-8"?>
    <bpmn:root xmlns:bpmn="http://www.omg.org/spec/BPMN/20100524/MODEL"><bpmn:task id="Task_3" name="Identify Application">
        <bpmn:documentation id="Task_3_Documentation">lorem ipsum 1</bpmn:documentation>
        <bpmn:incoming>SequenceFlow_4</bpmn:incoming>
        <bpmn:outgoing>SequenceFlow_3</bpmn:outgoing>
        <bpmn:ioSpecification>
            <bpmn:dataInput />
            <bpmn:dataOutput />
            <bpmn:inputSet>
                <bpmn:dataInputRefs>DataObject</bpmn:dataInputRefs>
            </bpmn:inputSet>
            <bpmn:outputSet>
                <bpmn:dataOutputRefs>DataObject_2</bpmn:dataOutputRefs>
            </bpmn:outputSet>
        </bpmn:ioSpecification>
        <bpmn:property id="Property_2" name="__targetRefPlaceholder" />
        <bpmn:property id="Property_0nnkfoz" name="__targetRef_placeholder" />
        <bpmn:dataInputAssociation id="DataAssociation_4-input">
            <bpmn:sourceRef>DataObject</bpmn:sourceRef>
            <bpmn:targetRef>Property_2</bpmn:targetRef>
        </bpmn:dataInputAssociation>
        <bpmn:dataInputAssociation id="DataInputAssociation_0ur4zio">
            <bpmn:sourceRef>DataObjectReference_0qo1qcm</bpmn:sourceRef>
            <bpmn:targetRef>Property_0nnkfoz</bpmn:targetRef>
        </bpmn:dataInputAssociation>
        <bpmn:dataOutputAssociation id="DataAssociation_3-output">
            <bpmn:targetRef>DataObject_2</bpmn:targetRef>
        </bpmn:dataOutputAssociation>
        <bpmn:dataOutputAssociation id="DataOutputAssociation_1rae1k7">
            <bpmn:targetRef>DataObjectReference_0v1uvlu</bpmn:targetRef>
        </bpmn:dataOutputAssociation>
    </bpmn:task>
<bpmn:task id="Task_2" name="Define Schedule">
            <bpmn:documentation id="Task_2_Documentation">lorem ipsum 2</bpmn:documentation>
            <bpmn:incoming>SequenceFlow</bpmn:incoming>
            <bpmn:outgoing>SequenceFlow_2</bpmn:outgoing>
            <bpmn:ioSpecification>
                <bpmn:dataOutput />
                <bpmn:inputSet />
                <bpmn:outputSet>
                    <bpmn:dataOutputRefs>DataObject_4</bpmn:dataOutputRefs>
                </bpmn:outputSet>
            </bpmn:ioSpecification>
            <bpmn:dataOutputAssociation id="DataAssociation_5-output">
                <bpmn:targetRef>DataObject_4</bpmn:targetRef>
            </bpmn:dataOutputAssociation>
            <bpmn:dataOutputAssociation id="DataOutputAssociation_1cvojts">
                <bpmn:targetRef>DataObjectReference_1t3shp5</bpmn:targetRef>
            </bpmn:dataOutputAssociation>
        </bpmn:task></bpmn:root>') as xml from dual)
--
-- here's the actual query
select xt.*
from diagram d
cross join xmltable(
            xmlnamespaces(default 'http://www.omg.org/spec/BPMN/20100524/MODEL'),
            '/root/task' passing xmltype(d.xml) 
            columns
            task varchar2(20) path '@id',
            name varchar2(100) path '@name',
            documentation varchar2(4000) path 'documentation'
            ) xt
;

Output:

TASK    NAME                  DOCUMENTATION
Task_3  Identify Application  lorem ipsum 1
Task_2  Define Schedule       lorem ipsum 2

Note that the xmlnamespaces() call should match your bpmn namespace declaration URL. Also, I’m just following the typical Oracle example of using a cross join with XMLTABLE(). If you want to run this for multiple rows in table DIAGRAM at the same time, you’ll need to modify that.