时间戳

首页 > 解决方案 > 如何使用 SQL 选择部分 CLOB 列

sql - 如何使用 SQL 选择部分 CLOB 列

问题描述

我正在尝试从包含 XML 的 CLOB 列中获取部分数据。

我需要这个，因为我想根据这些数据创建一个报告。

表结构：

图表（ID 号，XML clob）；

XML 示例：

<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>

我想得到这个结果：

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

提前致谢！

标签： sqloracle

解决方案

主要问题是您的 XML 无效。它缺少<?xml..>行，并且没有根元素，更不用说声明名称空间了。如果您想使用 Oracle 的 XML 函数，则需要添加它们。

但是，如果您修复您的 XML，那还不错。

-- 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
;

输出：

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

请注意，该xmlnamespaces()调用应与您的bpmn命名空间声明 URL 匹配。另外，我只是按照典型的 Oracle 示例使用 XMLTABLE() 进行交叉连接。如果要同时对表 DIAGRAM 中的多行运行此命令，则需要对其进行修改。

推荐阅读