PL/SQL: Loop Through XML Column and Extract Value From Duplicate Elements

Question

I have a SQL table that contains an XMLType column. I need to iterate through each row and extract values from the column.

The XML contain multiple children with the same will look like this:

<parent>
  <child>Test</child>
  <child>Test1</child>
  <child>Test2</child>
</parent>

My goal is to take the value from each child and append to a string. However, I am having issues extracting the value.

My current solution:

DECLARE
  sample VARCHAR(2000);
BEGIN
  FOR row IN (SELECT xml_column FROM table)
  LOOP
    FOR child in (SELECT EXTRACT('/parent/child') ...
    LOOP
      ....
    END LOOP;
  END LOOP;
END;

My first issue is I cannot seem to get the individual values. I've used both EXTRACT and EXTRACTVALUE but the best I've gotten is 'TEST1TEST2...'.

Second, if I try using row.XML_COLUMN it complains that it does not exist.

Jon Heller · Accepted Answer · 2020-02-21 06:23:38Z

2

Try using XMLTable to iterate through the values like a set.

Sample schema

--drop table table1;
create table table1 as
select xmltype('
    <parent>
        <child>Test</child>
        <child>Test1</child>
        <child>Test2</child>
    </parent>') xml_column
from dual;

PL/SQL Loop

begin
    for children in
    (
        select child
        from table1
        cross join xmltable
        (
            '/parent/child'
            passing xml_column
            columns child varchar2(100) path '/'
        )
    ) loop
        dbms_output.put_line(children.child);
    end loop;
end;
/

DBMS_OUTPUT Results

Test
Test1
Test2

answered Feb 21, 2020 at 6:23

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ghost013 Over a year ago

This worked, but could you explain the 'cross join' portion? I don't understand what this is doing.

Jon Heller Over a year ago

@ghost013 The cross join is pretty strange. It's really more like an OUTER APPLY, which is also a strange concept. Each row in TABLE1 is passed into XMLTABLE and the results are multiplied, so that one XML value/row can be expanded to multiple relational rows.

Collectives™ on Stack Overflow

PL/SQL: Loop Through XML Column and Extract Value From Duplicate Elements

1 Answer 1

Sample schema

PL/SQL Loop

DBMS_OUTPUT Results

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample schema

PL/SQL Loop

DBMS_OUTPUT Results

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related