将XML文件导入PostgreSQL数据库 [英] Import XML file into PostgreSQL Database
问题描述
几天前我设法将其导出3个不同的PgSQL表到一个XML文件中。现在，我要导入相同的文件。我搜索了大约2个小时，但只找到了将XML导入一个表的解决方案。这里是XML的结构
<？xml version = 1.0 encoding = UTF-8？>
<表1 Col1 = xxx Col2 = xxx>
< Table2 Col1 = xxx>
< Table3 Col1 = xxx Col2 = xxx Coln = xxx />
< / Table2>
< Table2 Col1 = xxx />
< Table2 Col1 = xxx>
< Table3 Col1 = xxx Col2 = xxx Coln = xxx />
< / Table2>
< / Table1>
表1包含表3，表2包含表3。
表是XMLWriterElements，列是XMLWriterAttributes。
更新：我解决了这个问题，想给我看看结果，如果有人说的相同或类似的问题：
$ reader = new XMLReader（）;
if（$ reader-> open（ tk.xml））{
while（$ reader-> read（））{
if（$ reader- > nodeType == XMLReader :: ELEMENT&& reader-> name =='表1'）{
$ knr = $ reader-> getAttribute（'Col1'）;
$ kname = $ reader-> getAttribute（’Col2’）;
$ SQL =;
$ SQL。=选择
（table1）.col1 AS col1，（table1）.col2 AS col1
FROM
table1
;
$ SQL。=插入表1（;
$ SQL。= col1，col1;
$ SQL。=）值（;
$ SQL。 =。$ col1。'，'。$ col1。';
$ SQL。=;。PHP_EOL;
echo $ SQL;
}
if（$ reader-> nodeType == XMLReader :: ELEMENT
&& reader-> name =='表2' ）{......}
if（$ reader-> nodeType == XMLReader :: ELEMENT
&& reader-> name =='表3' ）{......}
}
$ reader-> close（）;
}
我希望代码可以帮助某人。
绝对不可能使用XMLWriter导入它，因为那是XML输出。您需要 XMLReader ，它是用于XML的类似游标的拉式解析器。
您需要反转您用于输出的逻辑。遍历XML文档。当您看到一个新节点时，将其插入数据库，然后下降到数据库中并保留其ID记录，以便在为内部层插入外键引用时可以使用它。
您的逻辑将类似于以下伪代码说明：
xmldocument = [从XML创建新的XMLReader文字]
cur_table1_id = null;
cur_table2_id = null;
元素= xmldocument.get_next_element（）;
do {
if（element.name =='Table1'）
{
insert_table1（element）;
cur_table1_id = element.getAttribute（’id’）;
}
else if（element.name =='Table2'）
{
insert_table2（element，cur_table1_id）;
cur_table2_id = element.getAttribute（’id’）;
}
else if（element.name =='Table3'）
{
insert_table3（element，cur_table2_id）;
}
元素= get_next_element（）;
} while（element！= null）;
由您来阅读 XMLReader API文档和适当的示例，然后将粗略的逻辑概述转化为当前任务的实现。同样，您需要阅读PostgreSQL客户端界面上的PHP文档，以了解如何进行插入。
后者的免费提示：不不要使用 pg_query 和字符串串联/插值。使用PDO或 pg_query_params 。为何原因，请参见有关SQL注入的PHP手册。 / p>
让读者想知道为什么我忽略了close标签：在这种情况下，除非XML格式错误，否则它们并不重要。 < table3> 直接在< table1> 内，没有< table2> ，或在< table2> 内包含< table1> ，等等。无论如何，通过XML模式验证比在程序中更好地处理XML。
A few days ago I managed it to export 3 different PgSQL tables into one XML file. Now, I'd like to import the Same file. I've searched for about 2 hours but only found solutions for Importing a XML into one Table. Here ist the structure of the XML
<?xml version="1.0" encoding="UTF-8"?>
<Table1 Col1="xxx" Col2="xxx">
<Table2 Col1="xxx">
<Table3 Col1="xxx" Col2="xxx" Coln="xxx"/>
</Table2>
<Table2 Col1="xxx"/>
<Table2 Col1="xxx">
<Table3 Col1="xxx" Col2="xxx" Coln="xxx"/>
</Table2>
</Table1>
Table 1 contains Table 3 and table 2 contains Table 3.
The tables are XMLWriterElements, the columns XMLWriterAttributes.
UPDATE: I solved the problem and want to show you my results, if someone ha the same or similar problem:
$reader = new XMLReader();
if ($reader->open("tk.xml")) {
while($reader->read()) {
if ($reader->nodeType == XMLReader::ELEMENT &&reader->name == 'Table 1') {
$knr = $reader->getAttribute('Col1');
$kname = $reader->getAttribute('Col2');
$SQL = "";
$SQL .= "SELECT
(table1).col1 AS col1, (table1).col2 AS col1
FROM
table1
";
$SQL .= "INSERT INTO table1 (";
$SQL .= "col1, col1";
$SQL .= ") VALUES (";
$SQL .= "'".$col1."', '".$col1."'";
$SQL .= ");".PHP_EOL;
echo $SQL;
}
if ($reader->nodeType == XMLReader::ELEMENT
&&reader->name == 'Table 2') { ......}
if ($reader->nodeType == XMLReader::ELEMENT
&&reader->name == 'Table 3') { ......}
}
$reader->close();
}
I hope, thos code will help someone.
It is absolutely not possible to import this with XMLWriter, because that's for XML output. You want XMLReader, which is a cursor-like pull parser for XML.
You need to reverse the logic you used for the output. Iterate over the XML document. When you see a new node, insert it into the database, then descend into it and keep a record of its ID so you can use it when inserting foreign-key references for the inner layers.
Your logic will look something like the following pseudocode explanation:
xmldocument = [create a new XMLReader from the XML text]
cur_table1_id = null;
cur_table2_id = null;
element = xmldocument.get_next_element();
do {
if (element.name == 'Table1')
{
insert_table1(element);
cur_table1_id = element.getAttribute('id');
}
else if (element.name == 'Table2')
{
insert_table2(element, cur_table1_id);
cur_table2_id = element.getAttribute('id');
}
else if (element.name == 'Table3')
{
insert_table3(element, cur_table2_id);
}
element = get_next_element();
} while (element != null);
It's up to you to read the XMLReader API documentation and appropriate examples and turn that rough logic outline into an implementation of the task at hand. Similarly, you'll need to read the PHP documentation on the PostgreSQL client interface to figure out how to do the inserts.
Free tip on the latter: do not use pg_query and string concatenation/interpolation. Use PDO, or pg_query_params. For why, see the PHP manual on SQL injection.
For readers wondering why I ignored close tags: In this case they don't matter unless the XML is malformed, with a <table3> directly inside <table1> with no <table2>, or with a <table1> inside a <table2>, etc. Those cases are better handled by XML schema validation than they are procedurally in the code, anyway.
这篇关于将XML文件导入PostgreSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
