12

How can I write SQL to read an XML file into a PostgreSQL XML value?

PostgreSQL has a native XML data type with the XMLPARSE function to parse a text string to that type. It also has ways to read data from the filesystem; the COPY statement, among others.

But I don't see a way to write native PostgreSQL SQL statements to read the content from a filesystem entry and use that to populate an XML value. How can I do this?

3 Answers 3

10

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding).

This function works for any path, but needs to be created as superuser:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

select convert_from(stack.bytea_import('/tmp/test.xml'), 'utf8')::xml;
2
  • 1
    +1, thanks for pointing out that there are limits to the file-reading functions.
    – bignose
    Nov 21, 2011 at 12:04
  • 1
    +1 nice trick to circumvent pg_read_file(). The same can also be achieved with a temporary table and COPY - populate only 1 column of 1 row. Nov 21, 2011 at 12:10
4

The pg_read_binary_file function can do this.

It has limitations: new in PostgreSQL 9.1 or above; must be a session owned by database superuser; must read a file within the database directory or below. Those are acceptable in my use case.

So the following will work to create a native XML value from a file:

-- PostgreSQL 9.1 or later.
SELECT
    XMLPARSE(DOCUMENT convert_from(
        pg_read_binary_file('foo.xml'), 'UTF8'));

In PostgreSQL 8.3 – 9.0, the pg_read_file function can be used, with the additional limitation that you can't specify a file-specific encoding (it reads the file as text in the current session's encoding).

-- PostgreSQL earlier than 9.1.
SELECT
    XMLPARSE(DOCUMENT pg_read_file('foo.xml'));
1
  • note that the above solution requires your server was compiled --with-libxml
    – xzilla
    Jun 24, 2020 at 4:49
3

I have posted a complete implementation of what you are asking for in a recent answer on SO.

The key features are the xpath() function, pg_read_file(), array handling, plpgsql functions, ..

2
  • Rather different (and more heavyweight) than what I need in this case. But +1 for the good direction, thank you.
    – bignose
    Nov 21, 2011 at 12:01
  • It's not that heavyweight, my example is just very complete with redundant elements to demonstrate syntax variants. Nov 21, 2011 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.