clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601440 fiddles created (47992 in the last week).

CREATE TABLE xmldata ( id bigserial not null, documents xml ) ;
 hidden batch(es)

INSERT INTO xmldata(documents) VALUES (' <data> <test color="red">Red text</test> <test color="green">green</test> </data> ') ;
1 rows affected
 hidden batch(es)

SELECT id, xpath('//test', documents) FROM xmldata;
id xpath
1 {"<test color=\"red\">Red text</test>","<test color=\"green\">green</test>"}
 hidden batch(es)

WITH s0 AS ( -- Your original query SELECT id, xpath('//test', documents) AS x FROM xmldata ) , s1 AS ( -- We unnest the array (convert it to rows) SELECT id, unnest(x) AS xml_node FROM s0 ) SELECT id, xml_node, (regexp_matches(xml_node::text, '<test[^>]*>(.*)<\/test>'))[1] AS data FROM s1 ;
id xml_node data
1 <test color="red">Red text</test> Red text
1 <test color="green">green</test> green
 hidden batch(es)

-- Compacted version SELECT id, (regexp_matches(unnest(xpath('//test', documents))::text, '<test[^>]*>(.*)<\/test>'))[1] AS xml_node FROM xmldata
id xml_node
1 Red text
1 green
 hidden batch(es)