如何将从网页获取的XML数据最好地插入到PostgreSQL数据库中?
我正在使用Java,并需要一些帮助找到将此数据读入数据库的好方法。
如何将从网页获取的XML数据最好地插入到PostgreSQL数据库中?
我正在使用Java,并需要一些帮助找到将此数据读入数据库的好方法。
我有一个可以正常工作的实现,其中我使用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上的这个相关答案中所演示的那样。
Scott Bailey的这篇博客文章帮助了我。
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
标签都将成为表中的记录,相应的子标签则是属性。我从您的数据中收集了数据类型,但它们可能是错误的。我认为主要问题是没有自然主键,因此我添加了一个自动生成的主键。