文章/答案/技术大牛

发布

问将XML文件导入PostgreSQL
EN

Stack Overflow用户

提问于 2013-09-25 14:36:51

回答 4查看 54K关注 0票数 17

我确实有很多XML文件要导入到表xml_data中。

create table xml_data(result xml);

要做到这一点，我有一个简单的bash脚本和循环：

#!/bin/sh
FILES=/folder/with/xml/files/*.xml
for f in $FILES
do
  psql psql -d mydb -h myhost -U usr -c \'\copy xml_data from $f \'
done

但是，这将尝试将每个文件的每一行作为单独的行导入。这会导致错误：

ERROR:  invalid XML content
CONTEXT:  COPY address_results, line 1, column result: "<?xml version="1.0" encoding="UTF-8"?>"

我理解为什么它会失败，但我不知道如何让\copy立即将整个文件导入到单行中。

xml

bash

postgresql

Stack Overflow用户

发布于 2018-04-20 22:13:30

扩展@stefan-steiger的出色答案，这里有一个从包含多个兄弟节点(例如，针对特定的<synonym>父节点的多个<synomyms>元素)的子节点中提取XML元素的示例。

我用我的数据遇到了这个问题，并搜索了相当多的解决方案；他的回答对我来说是最有帮助的。

hmdb_metabolites_test.xml**:** 示例数据文件，

<?xml version="1.0" encoding="UTF-8"?>
<hmdb>
<metabolite>
  <accession>HMDB0000001</accession>
  <name>1-Methylhistidine</name>
  <synonyms>
    <synonym>(2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoic acid</synonym>
    <synonym>1-Methylhistidine</synonym>
    <synonym>Pi-methylhistidine</synonym>
    <synonym>(2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoate</synonym>
  </synonyms>
</metabolite>
<metabolite>
  <accession>HMDB0000002</accession>
  <name>1,3-Diaminopropane</name>
  <synonyms>
    <synonym>1,3-Propanediamine</synonym>
    <synonym>1,3-Propylenediamine</synonym>
    <synonym>Propane-1,3-diamine</synonym>
    <synonym>1,3-diamino-N-Propane</synonym>
  </synonyms>
</metabolite>
<metabolite>
  <accession>HMDB0000005</accession>
  <name>2-Ketobutyric acid</name>
  <synonyms>
    <synonym>2-Ketobutanoic acid</synonym>
    <synonym>2-Oxobutyric acid</synonym>
    <synonym>3-Methyl pyruvic acid</synonym>
    <synonym>alpha-Ketobutyrate</synonym>
  </synonyms>
</metabolite>
</hmdb>

撇开不说：原始XML文件在Document元素中有一个URL

<hmdb xmlns="http://www.hmdb.ca">

这使得xpath无法解析数据。它将运行(没有错误消息)，但是关系/表是空的：

[hmdb_test]# \i /mnt/Vancouver/Programming/data/hmdb/sql/hmdb_test.sql
DO
 accession | name | synonym 
-----------+------+---------

因为源文件是3.4GB，所以我决定使用sed编辑该行

sed -i '2s/.*hmdb xmlns.*/<hmdb>/' hmdb_metabolites.xml

添加2 (指示sed编辑“第2行”) --巧合的是，在本例中--将sed命令执行速度提高一倍。

我的postgres数据文件夹(PSQL：SHOW data_directory;)是

/mnt/Vancouver/Programming/RDB/postgres/postgres/data

因此，作为sudo，我需要在那里复制我的XML数据文件，并在chown中使用它以便在PostgreSQL中使用：

sudo chown postgres:postgres /mnt/Vancouver/Programming/RDB/postgres/postgres/data/hmdb_metabolites_test.xml

(**hmdb_test.sql**):脚本

DO $$DECLARE myxml xml;

BEGIN

myxml := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('hmdb_metabolites_test.xml'), 'UTF8'));

DROP TABLE IF EXISTS mytable;

-- CREATE TEMP TABLE mytable AS 
CREATE TABLE mytable AS 
SELECT 
    (xpath('//accession/text()', x))[1]::text AS accession
    ,(xpath('//name/text()', x))[1]::text AS name 
    -- The "synonym" child/subnode has many sibling elements, so we need to
    -- "unnest" them,otherwise we only retrieve the first synonym per record:
    ,unnest(xpath('//synonym/text()', x))::text AS synonym
FROM unnest(xpath('//metabolite', myxml)) x
;

END$$;

-- select * from mytable limit 5;
SELECT * FROM mytable;

执行，输出(在中)

[hmdb_test]# \i /mnt/Vancouver/Programming/data/hmdb/hmdb_test.sql

accession  |        name        |                         synonym                          
-------------+--------------------+----------------------------------------------------------
HMDB0000001 | 1-Methylhistidine  | (2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoic acid
HMDB0000001 | 1-Methylhistidine  | 1-Methylhistidine
HMDB0000001 | 1-Methylhistidine  | Pi-methylhistidine
HMDB0000001 | 1-Methylhistidine  | (2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoate
HMDB0000002 | 1,3-Diaminopropane | 1,3-Propanediamine
HMDB0000002 | 1,3-Diaminopropane | 1,3-Propylenediamine
HMDB0000002 | 1,3-Diaminopropane | Propane-1,3-diamine
HMDB0000002 | 1,3-Diaminopropane | 1,3-diamino-N-Propane
HMDB0000005 | 2-Ketobutyric acid | 2-Ketobutanoic acid
HMDB0000005 | 2-Ketobutyric acid | 2-Oxobutyric acid
HMDB0000005 | 2-Ketobutyric acid | 3-Methyl pyruvic acid
HMDB0000005 | 2-Ketobutyric acid | alpha-Ketobutyrate

[hmdb_test]#

票数 6

查看全部 4 条回答

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/19007884

复制

相似问题

问将XML文件导入PostgreSQL
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将XML文件导入PostgreSQLEN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将XML文件导入PostgreSQL
EN