1
CREATE OR REPLACE FUNCTION j_f_sync_from_xml_2()
RETURNS boolean AS
$BODY$
DECLARE
myxml    xml;
datafile text := 'ABBATE_EMANUELE_Lvl1F2Va_20160418-1759_3.xml';
BEGIN

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

CREATE TABLE public.james_2 AS
SELECT unnest(xpath('//@OPERATORE', t)) as Operatore, 
unnest(xpath('//@DATA', t)) as Data, unnest(xpath('//ObjectSelect/@NOME', 
t)) as Nome, unnest(xpath('//ObjectSelect/@TEMPO', t)) as Tempo, 
unnest(xpath('//ObjectSelect/@X', t)) as X, 
unnest(xpath('//ObjectSelect/@Y', t)) as Y,
unnest(xpath('//ObjectSelect/@Z', t)) as Z
FROM   unnest(xpath('/root/level', myxml)) x(t);
RETURN TRUE;
END;
$BODY$ language plpgsql;

This programme creates columns as XML type. How convert XML type to integer format? I tried this code: 

select  Tempo::text::int
from    james_2
cross join lateral
unnest(xpath('//ObjectSelect/@TEMPO', Tempo)) xp(T)

but give me an error: < not found

James
  • 97
  • 1
  • 1
  • 11

1 Answers1

0

You are trying to cast the complete xml that goes into your xpath call instead of the final output you aliased as xp(T). I would expect 

select  T::text::int
from    james_2
cross join lateral
unnest(xpath('//ObjectSelect/@TEMPO', Tempo)) xp(T)

to work. The ::text::int cast is the right idea I have used it my self in similar situations.

Eelke
  • 20,897
  • 4
  • 50
  • 76
  • [corrected URL] Yes; see also my answer, here (includes `xpath` examples): https://stackoverflow.com/questions/50054073/accessing-external-xml-files-as-variables-in-a-psql-script-sourced-from-a-bash/50071781#50071781 – Victoria Stuart May 01 '18 at 14:23