If you were to convert this to a sql statement and run it, like so:
WITH sample_data AS (SELECT XMLTYPE('<?xml version="1.0" ?>
<StatusUp>
<G_UUIDs>
<UUID>1 test 1</UUID>
<UUID>2 test 2</UUID>
<UUID>3 test 3 </UUID>
<UUID>4 test 4 </UUID>
</G_UUIDs>
</StatusUp>') x FROM dual)
SELECT p.*,
EXTRACTVALUE(VALUE(p), 'StatusUp/G_UUIDs/UUID/text()') AS uuid
FROM sample_data sd,
TABLE(XMLSEQUENCE(EXTRACT(sd.x, '//StatusUp/G_UUIDs/UUID'))) p;
it would be easy to spot the problem:
COLUMN_VALUE UUID
------------------------ ----------
<UUID>1 test 1</UUID>
<UUID>2 test 2</UUID>
<UUID>3 test 3 </UUID>
<UUID>4 test 4 </UUID>
I.e. you're trying to extract the node StatusUp/G_UUIDs/UUID from xml that only contains the node UUID. Instead, if you correct the node you're querying for, you get the right result:
DECLARE
X XMLTYPE := XMLTYPE('<?xml version="1.0" ?>
<StatusUp>
<G_UUIDs>
<UUID>1 test 1</UUID>
<UUID>2 test 2</UUID>
<UUID>3 test 3 </UUID>
<UUID>4 test 4 </UUID>
</G_UUIDs>
</StatusUp>');
BEGIN
FOR r IN (SELECT EXTRACTVALUE(VALUE(p), 'UUID/text()') AS uuid
FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//StatusUp/G_UUIDs/UUID'))) p)
LOOP
DBMS_OUTPUT.PUT_LINE('UUID' || r.uuid);
END LOOP;
END;
/
UUID1 test 1
UUID2 test 2
UUID3 test 3
UUID4 test 4
However, EXTRACT and EXTRACTVALUE are deprecated - you should be using XMLTABLE instead:
DECLARE
X XMLTYPE := XMLTYPE('<?xml version="1.0" ?>
<StatusUp>
<G_UUIDs>
<UUID>1 test 1</UUID>
<UUID>2 test 2</UUID>
<UUID>3 test 3 </UUID>
<UUID>4 test 4 </UUID>
</G_UUIDs>
</StatusUp>');
BEGIN
FOR r IN (SELECT *
FROM XMLTABLE('//StatusUp/G_UUIDs/UUID'
PASSING x
COLUMNS uuid varchar2(10) PATH '.'))
LOOP
DBMS_OUTPUT.PUT_LINE('UUID' || r.uuid);
END LOOP;
END;
/
UUID1 test 1
UUID2 test 2
UUID3 test 3
UUID4 test 4
Equivalent query that you should try running:
WITH sample_data AS (SELECT XMLTYPE('<?xml version="1.0" ?>
<StatusUp>
<G_UUIDs>
<UUID>1 test 1</UUID>
<UUID>2 test 2</UUID>
<UUID>3 test 3 </UUID>
<UUID>4 test 4 </UUID>
</G_UUIDs>
</StatusUp>') x FROM dual)
SELECT *
FROM sample_data sd,
XMLTABLE('//StatusUp/G_UUIDs/UUID'
PASSING sd.x
COLUMNS uuid varchar2(10) PATH '.');