Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
2

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?

share|improve this question
add comment (requires an account with 50 reputation)

3 Answers

up vote 3 down vote

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;
  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;
share|improve this answer
+1, thanks for pointing out that there are limits to the file-reading functions. – bignose Nov 21 '11 at 12:04
+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. – Erwin Brandstetter Nov 21 '11 at 12:10
add comment (requires an account with 50 reputation)
up vote 2 down vote

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, ..

share|improve this answer
Rather different (and more heavyweight) than what I need in this case. But +1 for the good direction, thank you. – bignose Nov 21 '11 at 12:01
It's not that heavyweight, my example is just very complete with redundant elements to demonstrate syntax variants. – Erwin Brandstetter Nov 21 '11 at 12:07
add comment (requires an account with 50 reputation)
up vote 1 down vote accepted

The limitations of pg_read_binary_file (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) 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'));
share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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