Home How can I convert XML type to integer?
Reply: 1

How can I convert XML type to integer?

James
1#
James Published in 2017-12-07 15:16:44Z
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

Eelke
2#
Eelke Reply to 2017-12-07 16:55:06Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30852 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO