PostgreSQL: How to loop through the xml array

Question

How to loop through the xml array. Here is the following code which I tried.

for c in select (unnest((xpath('//PayTypes/@value', ('<dummy_root><IncludeSettle/><StartTime value="2019-03-26 08:45:48.780"></StartTime><PayTypes value="1"/><PayTypes value="2"/></dummy_root>')::xml)))::text)
loop
raise notice '%', c;
end loop;

I am getting back the following error saying below:

ERROR:  loop variable of loop over rows must be a record variable or list of scalar variables
LINE 11: for c in select (unnest((xpath('//PayTypes/@value', ('<dummy...

Any help is really appreciated. Output I am expecting is printing values 1, 2.

See Why should I provide a Minimal Reproducible Example for a very simple SQL query? ... Question is a bit unclear without example data and expected results.. — Raymond Nijland
– Raymond Nijland, Commented Nov 26, 2019 at 9:42

TuTu94 · Accepted Answer · 2020-09-28 19:50:30Z

1

Maybe, you didn't declare the iterator c, I had the same problem with this cause. The code would look like (foo is the table):

    CREATE OR REPLACE FUNCTION your_method() RETURNS SETOF foo AS
    $BODY$
    DECLARE
        c xml; 
    BEGIN
        FOR c IN
            SELECT xml_column FROM foo
        LOOP
            raise notice '%', c;
        END LOOP;
        RETURN;
    END
    $BODY$
    LANGUAGE plpgsql;

    SELECT * FROM your_method();

answered Sep 28, 2020 at 19:50

TuTu94

112 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: How to loop through the xml array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related