Данные XML для базы данных PostgreSQL

Ответ 1

Postgres (спасибо Daniel Lyons за указание) встроенную поддержку XML, которую вы можете использовать для хранения вашей таблицы. Если вы хотите вручную испортить данные XML, существуют различные возможности представления данных XML в базе данных. Первый вопрос должен состоять в том, что если вы хотите получить очень общее решение, которое сможет хранить любой XML-документ или документ, специфичный для вашего домена (т.е. Только для документов XML определенной структуры). В зависимости от этого у вас будет очень гибкое универсальное представление, которое, тем не менее, сложнее запросить (необходимый SQL будет довольно сложным). Если у вас более конкретный подход, запросы будут проще, но вам нужно будет создавать новые таблицы или добавлять новые атрибуты к существующим талбам каждый раз, когда вы хотите сохранить другой тип документа или добавить поле в существующий документ; поэтому изменение схемы будет сложнее (что является одним из основных преимуществ XML). Эта презентация должна дать вам несколько идей, каковы разные возможности.

Кроме того, вы можете перейти к некоторому БД, поддерживающему Xquery, например DB2. Возможность естественного запроса с использованием XQuery, языка, ориентированного на обработку XML, упростит многое.

ОБНОВЛЕНИЕ: Учитывая ваш комментарий, ваши XML-данные (которые вы связаны с) являются совершенно реляционными. Его можно отобразить 1:1 в следующую таблицу:

CREATE TABLE mynt (
    ID          SERIAL     ,
    myntnafn    CHAR(3)    ,
    myntheiti   Varchar(255) ,
    kaupgengi   Decimal(15,2) ,
    midgengi    Decimal(15,2) ,
    solugengi   Decimal(15,2) ,
    dagsetning  TimeStamp      
)

Таким образом, любой тег mynt будет представлять собой запись в таблице и соответствующие подтеги атрибутов. Типы данных, которые я собирал из ваших данных, могут быть неправильными. Основная проблема - ИМО, что нет естественного первичного ключа, поэтому я добавил автогенерированный.

Ответ 2

У меня есть рабочая реализация, где я делаю все внутри PostgreSQL без дополнительных библиотек.

Вспомогательная функция разбора

CREATE OR REPLACE FUNCTION f_xml_extract_val(text, xml)
  RETURNS text AS
$func$
SELECT CASE
        WHEN $1 ~ '@[[:alnum:]_]+$' THEN
           (xpath($1, $2))[1]
        WHEN $1 ~* '/text()$' THEN
           (xpath($1, $2))[1]
        WHEN $1 LIKE '%/' THEN
           (xpath($1 || 'text()', $2))[1]
        ELSE
           (xpath($1 || '/text()', $2))[1]
       END;
$func$  LANGUAGE sql IMMUTABLE;

Обрабатывать несколько значений

Приведенная выше реализация не обрабатывает несколько атрибутов в одном xpath. Вот перегруженная версия f_xml_extract_val() для этого. С 3-м параметром вы можете выбрать one (первое), all или dist (разные) значения. Несколько значений объединяются в строку через запятую.

CREATE OR REPLACE FUNCTION f_xml_extract_val(_path text, _node xml, _mode text)
  RETURNS text AS
$func$
DECLARE
   _xpath text := CASE
                   WHEN $1 ~~ '%/'              THEN $1 || 'text()'
                   WHEN lower($1) ~~ '%/text()' THEN $1
                   WHEN $1 ~ '@\w+$'            THEN $1
                   ELSE                              $1 || '/text()'
                  END;
BEGIN
   -- fetch one, all or distinct values
   CASE $3
       WHEN 'one'  THEN RETURN (xpath(_xpath, $2))[1]::text;
       WHEN 'all'  THEN RETURN array_to_string(xpath(_xpath, $2), ', ');
       WHEN 'dist' THEN RETURN array_to_string(ARRAY(
            SELECT DISTINCT unnest(xpath(_xpath, $2))::text ORDER BY 1), ', ');
       ELSE RAISE EXCEPTION
          'Invalid $3: >>%<<', $3;
   END CASE;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_xml_extract_val(text, xml, text) IS '
Extract element of an xpath from XML document
Overloaded function to f_xml_extract_val(..)
$3 .. mode is one of: one | all | dist'

Вызов:

SELECT f_xml_extract_val('//city', x, 'dist');

Основная часть

Название целевой таблицы: tbl; чопорная. ключ: id:

CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$func$
DECLARE
   datafile text := 'path/to/my_file.xml';  -- only relative path in db dir
   myxml    xml  := pg_read_file(datafile, 0, 100000000); -- arbitrary 100 MB
BEGIN
   -- demonstrating 4 variants of how to fetch values for educational purposes
   CREATE TEMP TABLE tmp ON COMMIT DROP AS
   SELECT (xpath('//some_id/text()', x))[1]::text AS id   -- id is unique  
        , f_xml_extract_val('//col1', x)          AS col1 -- one value
        , f_xml_extract_val('//col2/', x, 'all')  AS col2 -- all values incl. dupes
        , f_xml_extract_val('//col3/', x, 'dist') AS col3 -- distinct values
   FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;

   -- 1.) DELETE?

   -- 2.) UPDATE
   UPDATE tbl t
   SET   (  col_1,   col2,   col3) =
         (i.col_1, i.col2, i.col3)
   FROM   tmp i
   WHERE  t.id = i.id
   AND   (t.col_1, t.col2, t.col3) IS DISTINCT FROM
         (i.col_1, i.col2, i.col3);

   -- 3.) INSERT NEW
   INSERT INTO tbl
   SELECT i.*
   FROM   tmp i
   WHERE  NOT EXISTS (SELECT 1 FROM tbl WHERE id = i.id);
END
$func$  LANGUAGE plpgsql;

Важные заметки

• Эта реализация проверяет первичный ключ, если вставленная строка уже существует, и обновляет в этом случае. Вставляются только новые строки.

• Я использую временный промежуточный стол, чтобы ускорить процедуру.

• Протестировано с Postgres 8.4, 9.0 и 9.1.

• XML должен быть правильно сформирован.

• pg_read_file() есть ограничения. Руководство:

  Использование этих функций разрешено только суперпользователям.

  А также:

  Только файлы в каталоге кластера базы данных и log_directory могут быть доступны.

Таким образом, вы должны поместить туда свой исходный файл - или создать символическую ссылку на ваш фактический файл/каталог.

Или вы можете предоставить файл через Java в вашем случае (я сделал все это в Postgres).

Или вы можете импортировать данные в 1 столбец 1 строки временной таблицы и взять их оттуда.

Или вы можете использовать lo_import как показано в этом ответе на dba.SE.

• SQL для чтения XML из файла в базу данных PostgreSQL

Этот пост в блоге Скотта Бэйли помог мне.

Ответ 3

PostgreSQL имеет XML-тип данных. Существует множество специфичных для XML функций, которые вы можете использовать для запроса и изменения данных, например, с помощью xpath.

Со стороны Java вы можете притворяться, что работаете только со строками, но знаете, что данные хорошо сформированы на выходе и не позволят вам хранить не-правильно сформированные данные.