在PostgreSQL中从文件插入XML [英] Insert XML from file in PostgreSQL

问题描述

我有几个XML文件，想要将其内容插入PostgreSQL表中。该表有两列-id（序列号）和xml类型列，我想在其中插入一个xml文件的内容（一行，一列=一个xml文件）。在文档中，我还没有找到如何从文件中插入xml。

是否有一些简单的方法？

Marek

解决方案

我刚刚编写了一个示例，说明如何使用纯文本文件执行此操作同样适用于 xml 文件。请参阅问题根据txt文件更新表行。

事实证明，您可以使用 psql ：

  regress => ;创建表xmldemo（id串行主键，等等xml）; 
 regress => \set test =`cat some.xml` 
 regress =>插入xmldemo（blah）值（：'test'）； 
 INSERT 0 1 
  

如果您要执行很多操作，则可能需要使用协同进程驱动 psql 或至少生成SQL并将其通过管道传输到 psql 是标准输入，因此您不必一遍又一遍地进行所有的连接设置/拆卸。

或者，使用shell进行操作：

 ＃！/ bin / bash 
 xmlfilename = $ 1 
 sep = $（printf'％04x％04x\n' $ RANDOM $ RANDOM）
 psql<< __ END__ 
插入mytable（myxmlcolumn）值（
 \ $ x $ {sep} \ $$（cat $ {xmlfilename}） \ $ x $ {sep} \ $
）; 
 __END__ 
  

生成随机分隔符可防止（不太可能）依赖

如果您使用正确的脚本语言和PostgreSQL，您将会更聪明，更快乐。

客户端库，例如具有 DBI 和 DBD :: Pg 的Perl，具有 psycopg2 或带有 Pg 宝石的Ruby，用于任何非常规的工作。在外壳中大量使用数据库会导致痛苦，痛苦和过度使用协同流程。

I have several XML files and want to insert their content into a PostgreSQL table. This table has two columns - id (type serial) and a xml type column, in which I want to insert the content of one xml file (one row, one column = one xml file). In the documentation I haven't found how to insert xml from a file.

Is there some easy way how to do it?

Marek

解决方案

Handily I just wrote an example of how to do this with plain text files that'll apply equally well to xml files. See the question updating table rows based on txt file.

It turns out you can do this with psql:

regress=> CREATE TABLE xmldemo(id serial primary key, blah xml);
regress=> \set test = `cat some.xml`
regress=> INSERT INTO xmldemo(blah) VALUES (:'test');
INSERT 0 1

If you're doing lots of this you might want to drive psql using a co-process or at least to generate SQL and pipe it into psql's stdin, so you don't have to do all that connection setup/teardown over and over.

Alternately, doing it with the shell:

#!/bin/bash
xmlfilename=$1
sep=$(printf '%04x%04x\n' $RANDOM $RANDOM)
psql <<__END__
INSERT INTO mytable(myxmlcolumn) VALUES (
\$x${sep}\$$(cat ${xmlfilename})\$x${sep}\$
);
__END__

The random separator generation is to protect against (unlikely) injection attacks that rely on knowing or guessing the dollar quoting separator tag.

You're going to be much saner and happier if you use a proper scripting language and PostgreSQL client library like Perl with DBI and DBD::Pg, Python with psycopg2 or Ruby with the Pg gem for any non-trivial work. Significant work with databases in the shell leads to pain, suffering, and excessive use of co-processes.

这篇关于在PostgreSQL中从文件插入XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！