Debug xml to postgres, a column myxml not exist?

Question

CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$BODY$
DECLARE
    myxml    xml;
    datafile text := 'Questo PC/Documenti/ABBATE_EMANUELE_Lvl1F2Va_20160418-1759.xml';
BEGIN

myxml := pg_read_file(datafile, 0, 100000000); 

END;
$BODY$ language plpgsql;


CREATE TEMP TABLE tmp AS
SELECT (xpath('//some_id/text()', x))[1]::text AS id
FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;

At the last line myxml gives an error the column not exists.

unnest(xpath('/xml/path/to/datum', myxml)) here myxml is variable from f_sync_from_xml()?.. — Vao Tsun
– Vao Tsun, Commented Nov 21, 2017 at 11:24
I want a procedure to create a table from xml file but myxml gives an error the column not exists — James
– James, Commented Nov 21, 2017 at 11:24

Vao Tsun · Accepted Answer · 2017-11-21 11:41:45Z

2

put statement inside the function body where you declare the variable myxml

CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$BODY$
DECLARE
    myxml    xml;
    datafile text := 'Questo PC/Documenti/ABBATE_EMANUELE_Lvl1F2Va_20160418-1759.xml';
BEGIN

myxml := pg_read_file(datafile, 0, 100000000); 

CREATE TEMP TABLE tmp AS
SELECT (xpath('//some_id/text()', x))[1]::text AS id
FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;
END;
$BODY$ language plpgsql;

also - if you jsut create a table on each execution - second execution will give you error, that tble exists

you can use IF NOT EXISTS:

t=# create temp table if not exists tmp as select now();
SELECT 1
Time: 57.280 ms
t=# create temp table if not exists tmp as select now();
NOTICE:  relation "tmp" already exists, skipping
CREATE TABLE AS
Time: 0.223 ms

edited Nov 21, 2017 at 11:41

answered Nov 21, 2017 at 11:26

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

4 Comments

James Over a year ago

How I create a table? I have to drop temp from the create table?

James Over a year ago

With if not exists give an error on as my code: CREATE TEMP TABLE if not exists tmp AS SELECT (xpath('//some_id/text()', x))[1]::text AS id FROM unnest(xpath('/xml/path/to/datum', myxml)) x; END; $BODY$ language plpgsql;

Vao Tsun Over a year ago

maybe you have older version with create tem table AS not supported yet?..

James Over a year ago

pgadmin III is my version

Collectives™ on Stack Overflow

Debug xml to postgres, a column myxml not exist?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related