Postgress - Read a XML file from a postgress table XML column

SELECT xmltable.* FROM xmldata, XMLTABLE('//ROWS/ROW' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, country_id text PATH 'COUNTRY_ID', size_sq_km float PATH 'SIZE[@unit = "sq_mi"]', size_other text PATH 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)', premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); SELECT xmltable.* FROM table1, XMLTABLE('//Project/Tasks/Task' PASSING xdata COLUMNS "Name" text); SELECT xmltable.* FROM "ProjXML", XMLTABLE('//Project/Tasks/Task' PASSING Document COLUMNS "Name" text, "BuildNumber" text PATH "../,

Postgres Table/ Filed returns from a Function/ Procedure

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, 'three'); INSERT INTO foo VALUES (4, 5, 'six'); CREATE OR REPLACE FUNCTION get_all_file_info() RETURNS SETOF bia_t_project_file_info AS $BODY$ DECLARE r bia_t_project_file_info%rowtype; BEGIN FOR r IN SELECT * FROM bia_t_project_file_info WHERE pfi_id > 0 loop -- public.bia_t_project_file_info -- can do some processing here RETURN NEXT r; -- return current row of SELECT END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; SELECT * FROM get_all_file_info(); -- drop function get_available_flightid create or replace FUNCTION get_available_flightid() RETURNS SETOF varchar(250) AS $BODY$ BEGIN RETURN QUERY SELECT pfi_filename FROM project WHERE pfi_id > 0; -- Since execution is not finished, we can check whether rows were returned -- and raise exception if not. IF NOT FOUND THEN RAISE EXCEPTION 'No flight at %.', $1; END IF; RETURN; END $BODY$ LANGUAGE plpgsql; -- Returns available flights or raises exception if there are no -- available flights. SELECT * FROM get_available_flightid(CURRENT_DATE);