Re: How to import XML large file in Postgresql

Hi Gennaro,

if the file is accessible from the server there are functions to access the file directly https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE or via large objects https://www.postgresql.org/docs/current/static/lo-funcs.html

From a client, beside what Gunnar already mentioned, and esp. if dealing with bigger files, a solution utilising large objects may be better suited. In the psql-client there a special \lo_import command to load a local file into a large object. Later you need to read the lo and put the content in the xml column in your table. For that I created a quick stored procedure:

CREATE OR REPLACE FUNCTION import.xml_import(loid oid, p_unlink boolean DEFAULT true) RETURNS xml
LANGUAGE plpgsql
AS $$
declare
content bytea;
lfd integer;
lsize integer;
begin
lfd := lo_open(loid,262144); --INV_READ
lsize := lo_lseek(lfd,0,2);
perform lo_lseek(lfd,0,0);
content := loread(lfd,lsize);
perform lo_close(lfd);

if p_unlink then
perform lo_unlink(loid);
end if;

return xmlparse(document convert_from(content,'UTF8'));
end;
$$;

DROP TABLE IF EXISTS import.xml_data;
CREATE TABLE import.xml_data (
name TEXT PRIMARY KEY,
xml_import XML
);

\lo_import '~/MyFile.xml'
\set tmp_lo_id :LASTOID
INSERT INTO import.xml_data VALUES ('MyFile.xml', import.xml_import(:tmp_lo_id, true));

Hope this helps
Best regards

Tobias

> Am 23.06.2016 um 22:24 schrieb Gennaro Sivero <gensiv08(at)gmail(dot)com>:
>
> Hello
> I need to import an XML large file into a database table .
>
>
> I've created a table with the following command:
>
>
> CREATE TABLE mytable(xmldata xml)
> A file containing data is on my desktop. How would I import this file into my table?
>
> Is there an existing utility, like SQLLoader in Oracle that does this?