Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table your_table(xmlcolumn) as
select xmltype('<?xml version="1.0" encoding="UTF-8"?>
<Root>
    <characteristics>
        <motor>
            <fuel>2</fuel>
        </motor>
    </characteristics>
    <ValuesList>
        <CarValues>
            <CarValue>
                <value>1</value>
                <label>Diesel</label>
            </CarValue>
            <CarValue>
                <value>2</value>
                <label>Unleaded petrol</label>
            </CarValue>
        </CarValues>
    </ValuesList>
</Root>
')
from dual
1 rows affected

SELECT
extract(t.xmlColumn,'//fuel/text()').getStringVal() a
,extractValue(t.xmlColumn,'//fuel') b
FROM your_table t


A
B


2
2


SELECT
extract(t.xmlColumn,'//fuel/text()').getStringVal() a
,extractValue(t.xmlColumn,'//fuel') b
,extract(t.xmlColumn, '//CarValue/label[../value=//fuel/text()]/text()') c
,extractValue(t.xmlColumn, '//CarValue/label[../value=//fuel/text()]') c
FROM your_table t


A
B
C
C


2
2
Unleaded petrol
Unleaded petrol


SELECT
XMLQuery('//fuel/text()' passing xmlColumn returning content) a
,XMLQuery('//CarValue/label[../value=//fuel/text()]/text()' passing xmlColumn returning content) a
FROM your_table t


A
A


2
Unleaded petrol


SELECT x.a, x.b
FROM your_table t
CROSS APPLY XMLTable(
  '/'
  passing t.xmlColumn
  columns a number path '//fuel',
    b varchar2(30) path '//CarValue/label[../value=//fuel/text()]'
) x


A
B


2
Unleaded petrol


SELECT x1.fuel, x2.label
FROM your_table t
CROSS JOIN XMLTable(
  '//fuel'
  passing t.xmlColumn
  columns fuel number path '.'
) x1
JOIN XMLTable(
  '//CarValue'
  passing t.xmlColumn
  columns value number path 'value',
    label varchar2(30) path 'label'
) x2 ON x2.value = x1.fuel


FUEL
LABEL


2
Unleaded petrol