将XML数据导入到PostgreSQL数据库中

Question

如何将从网页获取的XML数据最好地插入到PostgreSQL数据库中？
我正在使用Java，并需要一些帮助找到将此数据读入数据库的好方法。

- user622194

---

当你说将 XML 数据插入数据库时，你是想将完整的 XML（以字符串/文本形式）插入，还是解析字段并插入每个字段？ - kdabir

只是将XML文件添加到数据库中，还是解析其中的值并保存到特定的表中？ - EO2

这是数据 http://www.sedlabanki.is/?PageID=289是的，我有一个表格，这些数据应该放入其中，该表格具有与每个<mynt>相同的列。 - user622194

Answer 1

我有一个可以正常工作的实现，其中我使用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()。通过第三个参数，您可以选择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上的这个相关答案中所演示的那样。

• 从文件读取XML到PostgreSQL数据库的SQL

Scott Bailey的这篇博客文章帮助了我。

- Erwin Brandstetter

---

如果XML行包含保留字符，例如“'”或“＆”，我该怎么办？ - Mohammed Falha

Answer 2

Postgres（感谢Daniel Lyons指出）具有本地XML支持，您可以使用它来存储表格。然而，如果您想要手动分析XML数据，则有不同的可能性来表示数据库中的XML数据。第一个问题是，您是否需要非常通用的解决方案，该解决方案将能够存储任何XML文档或仅限于特定领域（即仅允许特定结构的XML文档）。根据这一点，您将拥有非常灵活且通用的表示方式，但是查询会更加困难（所需SQL将相当复杂）。如果您采取更具体的方法，则查询将更简单，但是每次想要存储另一种类型的文档或向现有文档添加字段时，您都需要创建新表格或向现有表格添加新属性;因此更改架构将更加困难（这是XML的一个重要优势）。此演示文稿应该会给您一些关于不同可能性的想法。

此外，您可以考虑切换到支持Xquery的某些数据库，例如DB2。原生查询使用针对处理XML的语言XQuery将大大简化事情。

更新：考虑到您的评论，您的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标签都将成为表中的记录，相应的子标签则是属性。我从您的数据中收集了数据类型，但它们可能是错误的。我认为主要问题是没有自然主键，因此我添加了一个自动生成的主键。

- Janick Bernet

---

自从8.2版本起，PostgreSQL就拥有了本地的XML支持。请参考：http://www.postgresql.org/docs/current/static/datatype-xml.html和http://www.postgresql.org/docs/8.2/static/datatype-xml.html。 - Daniel Lyons

抱歉，可能没有给出问题的答案。 - Janick Bernet

@Daniel：我现在已经更新了它，以反映PostgreSQL具有XML的事实。然而，从我收集到的信息来看，DB2仍然具有拥有XQuery的优势。 - Janick Bernet

我打算使用Postgres，但是我在评论中提供的XML数据是我将使用的唯一类型的数据。我需要插入表格的所有XML都将像这样的xml一样，所以我永远不需要添加新属性或任何其他内容。我将继续在Google上搜索并尝试找到一些东西，但如果您有任何想法可以用来完成此操作，请分享 :）。（抱歉，如果我的英语不好，这不是我的强项）。 - user622194

@MaxvonHippel 今天它们似乎在工作，但无论如何，我无法编辑四年前的评论中的链接，所以最好你直接留下更正后的链接。 - Daniel Lyons

Answer 3

PostgreSQL拥有一个XML数据类型，该数据库提供了很多特定于XML的函数，你可以使用这些函数来查询和修改数据，例如使用xpath。

从Java端来看，你可以将它视为字符串处理，但是需要知道输出的数据格式良好，并且它不会让你存储格式不良好的数据。

- Daniel Lyons