I need to be able to export/import XML data from/to a PostgresQL database. I've reviewed the documentation, and determined that I can readily export XML using a query or a table. What would be the simplest way to re-import the same XML into the same database? I prefer not using a third-party tool, but will handle any programming in PG functions and Java/JDBC.
I will vote on anyone providing a useful information.
Create a "staging" table that contains one row for each XML that you want to import.
Then use the XML functions (e.g. xpath_table) to slice that XML column into rows and columns. Something like this:
-- make sure xpath_table is available
create extension xml2;
-- create XML staging table
create table xml_test (id integer, xml text);
-- create sample XML document
insert into xml_test (id, data)
values
(1, '<person-list>
<person>
<id>42</id>
<firstname>Arthur</firstname>
<lastname>Dent</lastname>
</person>
<person>
<id>43</id>
<firstname>Zaphod</firstname>
<lastname>Beeblebrox</lastname>
</person>
</person-list>'
);
Now if your target table is e.g.:
create table person
(
id integer not null primary key,
firstname text,
lastname text
);
you can use:
insert into person (id, firstname, lastname)
select person_id, firstname, lastname
from xpath_table('id', 'xml', 'xml_test',
'/person-list/person/id|/person-list/person/firstname|/person-list/person/lastname', 'id=1')
as t (docid integer, person_id integer, firstname text, lastname text)
The last parameter to the function id=1 is the row that is selected from the table containing the XML column. If you have more than one document in there, you need to pick the one that matches the xpath expressions
You can also create a similar view without the xml2 module, using the core XML functions. See the link user272735 has posted in the comments.