I have several large (7GB+) xml files to get into an SQL database.
The xml-files can contain up to 500 000 subrecords which I want to be able to query in the database.
insert into rawxml (xml) select XMLPARSE (DOCUMENT CONVERT_FROM(PG_READ_BINARY_FILE('FOO.xml' ), 'UTF8'));
If it were possible, each file would be one huge record in the table which can then be unpacked using XPATH.
The options I am considering is :
1. Unpack the individual records (will be more than 50 million) using something like python with lxml and psycopg2 and insert them after dropping all indexes and triggers on the table
2. Unpack the individual records and write a (very) large tsv-file and then insert it using 'copy'
It would be convenient If I could use the present xml files as 'foreign tables' and parse them using the xpath-capabilities of PostgreSQL.
Is this possible?
Of not, what would be the most efficient way of getting the individual xml-records xml into a table into a xml-type field?
The documentation did not give me a clear answer.
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)