文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >将XML文件导入PostgreSQL

问将XML文件导入PostgreSQL
EN

Stack Overflow用户
提问于 2013-09-25 14:36:51
回答 4查看 54K关注 0票数 17

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

代码语言：javascript
复制
create table xml_data(result xml);

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

代码语言：javascript
复制
#!/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

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

代码语言：javascript
复制
ERROR:  invalid XML content
CONTEXT:  COPY address_results, line 1, column result: "<?xml version="1.0" encoding="UTF-8"?>"

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

postgresql
xml
bash
EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2013-09-25 19:42:18

我将尝试一种不同的方法:将XML文件直接读入plpgsql函数中的变量，然后从那里开始。应该是，要快得多，健壮得多。

代码语言：javascript
复制
CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$BODY$
DECLARE
    myxml    xml;
    datafile text := 'path/to/my_file.xml';
BEGIN
   myxml := pg_read_file(datafile, 0, 100000000);  -- arbitrary 100 MB max.

   CREATE TEMP TABLE tmp AS
   SELECT (xpath('//some_id/text()', x))[1]::text AS id
   FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;
   ...

您需要超级用户特权，并且文件必须是数据库服务器的本地，位于可访问的目录中。

完整的代码示例，并提供更多解释和链接：

票数 15
EN

Stack Overflow用户

发布于 2015-10-19 10:15:49

亡灵术:对于那些需要一个工作实例的人：

代码语言：javascript
复制
DO $$
   DECLARE myxml xml;
BEGIN

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

DROP TABLE IF EXISTS mytable;
CREATE TEMP TABLE mytable AS 

SELECT 
     (xpath('//ID/text()', x))[1]::text AS id
    ,(xpath('//Name/text()', x))[1]::text AS Name 
    ,(xpath('//RFC/text()', x))[1]::text AS RFC
    ,(xpath('//Text/text()', x))[1]::text AS Text
    ,(xpath('//Desc/text()', x))[1]::text AS Desc
FROM unnest(xpath('//record', myxml)) x
;

END$$;


SELECT * FROM mytable;

或者噪音更小

代码语言：javascript
复制
SELECT 
     (xpath('//ID/text()', myTempTable.myXmlColumn))[1]::text AS id
    ,(xpath('//Name/text()', myTempTable.myXmlColumn))[1]::text AS Name 
    ,(xpath('//RFC/text()', myTempTable.myXmlColumn))[1]::text AS RFC
    ,(xpath('//Text/text()', myTempTable.myXmlColumn))[1]::text AS Text
    ,(xpath('//Desc/text()', myTempTable.myXmlColumn))[1]::text AS Desc
    ,myTempTable.myXmlColumn as myXmlElement
FROM unnest(
    xpath
    (    '//record'
        ,XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'))
    )
) AS myTempTable(myXmlColumn)
;

使用此示例XML文件(MyData.xml)：

代码语言：javascript
复制
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set>
    <record>
        <ID>1</ID>
        <Name>A</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Address record</Text>
        <Desc>Returns a 32-bit IPv4 address, most commonly used to map hostnames to an IP address of the host, but it is also used for DNSBLs, storing subnet masks in RFC 1101, etc.</Desc>
    </record>
    <record>
        <ID>2</ID>
        <Name>NS</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Name server record</Text>
        <Desc>Delegates a DNS zone to use the given authoritative name servers</Desc>
    </record>
</data-set>

注意：

MyData.xml需要位于PG_Data目录( pg_stat目录的父目录)中。

例如/var/lib/postgresql/9.3/main/MyData.xml

这需要PostGreSQL 9.1+

总的来说，您可以在没有文件的情况下实现它，比如：

代码语言：javascript
复制
SELECT 
     (xpath('//ID/text()', myTempTable.myXmlColumn))[1]::text AS id
    ,(xpath('//Name/text()', myTempTable.myXmlColumn))[1]::text AS Name 
    ,(xpath('//RFC/text()', myTempTable.myXmlColumn))[1]::text AS RFC
    ,(xpath('//Text/text()', myTempTable.myXmlColumn))[1]::text AS Text
    ,(xpath('//Desc/text()', myTempTable.myXmlColumn))[1]::text AS Desc
    ,myTempTable.myXmlColumn as myXmlElement 
    -- Source: https://en.wikipedia.org/wiki/List_of_DNS_record_types
FROM unnest(xpath('//record', 
 CAST('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set>
    <record>
        <ID>1</ID>
        <Name>A</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Address record</Text>
        <Desc>Returns a 32-bit IPv4 address, most commonly used to map hostnames to an IP address of the host, but it is also used for DNSBLs, storing subnet masks in RFC 1101, etc.</Desc>
    </record>
    <record>
        <ID>2</ID>
        <Name>NS</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Name server record</Text>
        <Desc>Delegates a DNS zone to use the given authoritative name servers</Desc>
    </record>
</data-set>
' AS xml)   
)) AS myTempTable(myXmlColumn)
;

注意，与MS不同，xpath text()对空值返回NULL，而不是空字符串。

如果出于任何原因需要显式检查NULL的存在，则可以使用[not(@xsi:nil="true")]，需要向其传递名称空间数组，否则会出现错误(但是，可以省略所有名称空间，但xsi除外)。

代码语言：javascript
复制
SELECT 
     (xpath('//xmlEncodeTest[1]/text()', myTempTable.myXmlColumn))[1]::text AS c1

    ,(
    xpath('//xmlEncodeTest[1][not(@xsi:nil="true")]/text()', myTempTable.myXmlColumn
    ,
    ARRAY[
        -- ARRAY['xmlns','http://www.w3.org/1999/xhtml'], -- defaultns
        ARRAY['xsi','http://www.w3.org/2001/XMLSchema-instance'],
        ARRAY['xsd','http://www.w3.org/2001/XMLSchema'],        
        ARRAY['svg','http://www.w3.org/2000/svg'],
        ARRAY['xsl','http://www.w3.org/1999/XSL/Transform']
    ]
    )
    )[1]::text AS c22


    ,(xpath('//nixda[1]/text()', myTempTable.myXmlColumn))[1]::text AS c2 
    --,myTempTable.myXmlColumn as myXmlElement
    ,xmlexists('//xmlEncodeTest[1]' PASSING BY REF myTempTable.myXmlColumn) AS c1e
    ,xmlexists('//nixda[1]' PASSING BY REF myTempTable.myXmlColumn) AS c2e
    ,xmlexists('//xmlEncodeTestAbc[1]' PASSING BY REF myTempTable.myXmlColumn) AS c1ea
FROM unnest(xpath('//row', 
     CAST('<?xml version="1.0" encoding="utf-8"?>
    <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <row>
        <xmlEncodeTest xsi:nil="true" />
        <nixda>noob</nixda>
      </row>
    </table>
    ' AS xml)   
    )
) AS myTempTable(myXmlColumn)
;

还可以通过以下操作检查XML-text中是否包含一个字段

代码语言：javascript
复制
 ,xmlexists('//xmlEncodeTest[1]' PASSING BY REF myTempTable.myXmlColumn) AS c1e

例如，当您将XML-值传递给CRUD的存储过程/函数时。(见上文)

另外，请注意，在XML中传递空值的正确方法是<elementName xsi:nil="true" />，而不是<elementName />或nothing。没有正确的方法在属性中传递NULL (您只能省略该属性，但是很难/很慢地推断大型数据集中的列数及其名称)。

例如：

代码语言：javascript
复制
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table>
    <row column1="a" column2="3" />
    <row column1="b" column2="4" column3="true" />
</table>

(更紧凑，但如果您需要导入它，特别是从XML-具有多个GB数据的文件-参见堆栈溢出数据转储中的一个很好的例子)

代码语言：javascript
复制
SELECT 
     myTempTable.myXmlColumn
    ,(xpath('//@column1', myTempTable.myXmlColumn))[1]::text AS c1
    ,(xpath('//@column2', myTempTable.myXmlColumn))[1]::text AS c2
    ,(xpath('//@column3', myTempTable.myXmlColumn))[1]::text AS c3
    ,xmlexists('//@column3' PASSING BY REF myTempTable.myXmlColumn) AS c3e
    ,case when (xpath('//@column3', myTempTable.myXmlColumn))[1]::text is null then 1 else 0 end AS is_null 
FROM unnest(xpath('//row', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table>
    <row column1="a" column2="3" />
    <row column1="b" column2="4" column3="true" />
</table>'
))  AS myTempTable(myXmlColumn)
票数 23
EN

Stack Overflow用户

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

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

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

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

代码语言：javascript
复制
<?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

代码语言：javascript
复制
<hmdb xmlns="http://www.hmdb.ca">

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

代码语言：javascript
复制
[hmdb_test]# \i /mnt/Vancouver/Programming/data/hmdb/sql/hmdb_test.sql
DO
 accession | name | synonym 
-----------+------+---------

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

代码语言：javascript
复制
sed -i '2s/.*hmdb xmlns.*/<hmdb>/' hmdb_metabolites.xml

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

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

代码语言：javascript
复制
/mnt/Vancouver/Programming/RDB/postgres/postgres/data

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

代码语言：javascript
复制
sudo chown postgres:postgres /mnt/Vancouver/Programming/RDB/postgres/postgres/data/hmdb_metabolites_test.xml

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

代码语言：javascript
复制
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;

执行，输出(在中)

代码语言：javascript
复制
[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
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/19007884

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档