SQL do odczytu XML z pliku do bazy danych PostgreSQL

Jak napisać SQL, aby odczytać plik XML w XMLwartości PostgreSQL ?

PostgreSQL ma rodzimy typ danych XML z XMLPARSEfunkcją analizowania ciągu tekstowego do tego typu. Ma także sposoby odczytu danych z systemu plików; COPYstwierdzenie, między innymi.

Ale nie widzę sposobu na pisanie natywnych instrukcji SQL PostgreSQL w celu odczytania zawartości pozycji systemu plików i użycia jej do wypełnienia XMLwartości. W jaki sposób mogę to zrobić?

Podobnie do tej odpowiedzi na poprzednie pytanie i jeśli nie chcesz ograniczeńpg_read_file() (w skrócie: pg_read_filenie można odczytać plików poza katalogiem bazy danych i odczytuje tekst w kodowaniu znaków bieżącej sesji).

Ta funkcja działa dla dowolnej ścieżki, ale musi zostać utworzona jako administrator:

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 został wprowadzony w wersji 9.4, więc dla starszych wersji potrzebujesz:

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;$$;

następnie:

select convert_from(stack.bytea_import('/tmp/test.xml'), 'utf8')::xml;

pg_read_binary_fileFunkcja może to zrobić.

Ma ograniczenia: nowość w PostgreSQL 9.1 lub nowszym; musi być sesją należącą do administratora bazy danych; musi odczytać plik w katalogu bazy danych lub poniżej. Są one dopuszczalne w moim przypadku użycia.

Tak więc następujące czynności będą działać, aby utworzyć natywną XMLwartość z pliku:

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

W PostgreSQL 8.3 - 9.0 pg_read_filemożna korzystać z tej funkcji, z dodatkowym ograniczeniem, że nie można określić kodowania specyficznego dla pliku (odczytuje plik jako tekst w kodowaniu bieżącej sesji).

-- PostgreSQL earlier than 9.1.
SELECT
    XMLPARSE(DOCUMENT pg_read_file('foo.xml'));

W ostatniej odpowiedzi na SO opublikowałem pełną implementację tego, o co prosisz .

Kluczowe cechy to xpath()funkcja, pg_read_file()obsługa tablicy, funkcje plpgsql, ..