By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dsa_source(dsa_name varchar2(10), revision number, dsa_source varchar2(4000));
insert into dsa_source (dsa_name, revision, dsa_source)
values ('A', 1, '<root>
<PreProcessor><statements>create temp</statements></PreProcessor>
<PostProcessor><statements>should be ignored ignored</statements></PostProcessor>
</root>');
1 rows affected
insert into dsa_source (dsa_name, revision, dsa_source)
values ('A', 2, '<root>
<PreProcessor><statements>create temp</statements></PreProcessor>
<PostProcessor><statements>should be ignored?</statements></PostProcessor>
</root>');
1 rows affected
insert into dsa_source (dsa_name, revision, dsa_source)
values ('A', 3, '<root>
<sub>
<PreProcessor><statements>abc create def temp ghi</statements></PreProcessor>
<PostProcessor><statements>should be included</statements></PostProcessor>
</sub>
</root>');
1 rows affected
insert into dsa_source (dsa_name, revision, dsa_source)
values ('B', 1, '<root>
<PreProcessor><statements>create temp</statements></PreProcessor>
<PostProcessor><statements>should be ignored</statements></PostProcessor>
</root>');
1 rows affected
insert into dsa_source (dsa_name, revision, dsa_source)
values ('B', 2, '<root>
<PreProcessor><statements>create temp</statements></PreProcessor>
<PostProcessor><statements>should be included</statements></PostProcessor>
</root>');
1 rows affected
-- original
SELECT t.DSA_NAME as "DSA NAME",
t.REVISION as REVISION,
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS "PreProcessor Temp Table",
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PostProcessor/statements/text()').getStringVal() AS "PostProcessor Temp Table"
FROM
DSA_SOURCE t
where XMLCAST (XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS VARCHAR2) like '%create%temp%'
and t.REVISION IN (SELECT MAX(REVISION)
FROM DSA_SOURCE
GROUP BY DSA_NAME);
ORA-00906: missing left parenthesis
-- just with varchar2 size
SELECT t.DSA_NAME as "DSA NAME",
t.REVISION as REVISION,
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS "PreProcessor Temp Table",
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PostProcessor/statements/text()').getStringVal() AS "PostProcessor Temp Table"
FROM
DSA_SOURCE t
where XMLCAST (XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS VARCHAR2(100)) like '%create%temp%'
and t.REVISION IN (SELECT MAX(REVISION)
FROM DSA_SOURCE
GROUP BY DSA_NAME);
ORA-00932: inconsistent datatypes: expected - got -
SELECT t.DSA_NAME as "DSA NAME",
t.REVISION as REVISION,
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS "PreProcessor Temp Table",
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PostProcessor/statements/text()').getStringVal() AS "PostProcessor Temp Table"
FROM
DSA_SOURCE t
where XMLCAST (XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()') AS VARCHAR2(100)) like '%create%temp%'
and t.REVISION IN (SELECT MAX(REVISION)
FROM DSA_SOURCE
GROUP BY DSA_NAME);
| DSA NAME | REVISION | PreProcessor Temp Table | PostProcessor Temp Table |
|---|---|---|---|
| A | 2 | create temp | should be ignored? |
| A | 3 | abc create def temp ghi | should be included |
| B | 2 | create temp | should be included |
SELECT t.DSA_NAME as "DSA NAME",
t.REVISION as REVISION,
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS "PreProcessor Temp Table",
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PostProcessor/statements/text()').getStringVal() AS "PostProcessor Temp Table"
FROM
DSA_SOURCE t
where XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() like '%create%temp%'
and t.REVISION IN (SELECT MAX(REVISION)
FROM DSA_SOURCE
GROUP BY DSA_NAME);
| DSA NAME | REVISION | PreProcessor Temp Table | PostProcessor Temp Table |
|---|---|---|---|
| A | 2 | create temp | should be ignored? |
| A | 3 | abc create def temp ghi | should be included |
| B | 2 | create temp | should be included |
select t.dsa_name as "DSA NAME",
t.revision as revision,
xmlquery('//PreProcessor/statements/text()'
passing xmltype(t.dsa_source)
returning content) as "PreProcessor Temp Table",
xmlquery('//PostProcessor/statements/text()'
passing xmltype(t.dsa_source)
returning content) as "PostProcessor Temp Table"
from dsa_source t
where xmlexists('//PreProcessor/statements[matches(text(), ''create.*temp'')]'
passing xmltype(t.dsa_source))
and (t.dsa_name, t.revision) in (
select dsa_name, max(revision)
from dsa_source
group by dsa_name
);
| DSA NAME | REVISION | PreProcessor Temp Table | PostProcessor Temp Table |
|---|---|---|---|
| A | 3 | abc create def temp ghi | should be included |
| B | 2 | create temp | should be included |