Import XML file into PostgreSQL Database

Question

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.

A database is really a single flat table with rows and columns. Tables break the large database into views which are subsets of the single flat table. Your XML have nested tables which is difficult to enter into database. I would create a single table which contains Col1, Col2, and Coln and then enter data into the single table rather than enter the data into three seperate tables (table1, table 2, and table 3). — jdweng
– jdweng, Commented Aug 19, 2015 at 10:40
You've pasted code in an update, but no indication of what problem you're having, errors you're getting, etc. At a quick glance I can see a number of problems: You seem to be using MySQL-style identifier quoting with , not ANSI SQL style quoting with "`. You are also, against my explicit advice, concatenating values in your SQL and leaving you wide open for SQL injection if you execute that SQL. — Craig Ringer
– Craig Ringer, Commented Aug 19, 2015 at 14:23
If you are still stuck with whatever problem you are having with that code you pasted in an edit, please post a new question with a link back to this one for context, the subsection of code that you're having issues with, a description of the problem, the exact error message(s) you're getting, etc. You will probably find that you solve it yourself in the process of writing up the problem. (I can't follow your code very well, there seems to be lots of duplication; did you paste part of it twice, maybe?) — Craig Ringer
– Craig Ringer, Commented Aug 19, 2015 at 14:24
Okay, sorry, I din't understood that part, as I'm relatively new to PHP. And I forgot to save the edit, where I wrote the problem. I rework it and post my results later — pguetschow
– pguetschow, Commented Aug 19, 2015 at 14:25
Okay, I rethought it and wrote the working code in the OP, maybe there are things i can make better? Sorry for my questions, but i started working with PHP 3 Weeks ago — pguetschow
– pguetschow, Commented Aug 20, 2015 at 6:34

Community · Accepted Answer · 2017-05-23 12:06:15Z

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.

Oh, yeah, I meant XMLReader. My bad. I will try this out. Thank you in advance for your fast and professional anwser. Edit: Sadly i already reched my 30 votes/ day. Will upvote this tomorrow

Collectives™ on Stack Overflow

Import XML file into PostgreSQL Database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related