I have XML format file for account table created using query as: SELECT table_to_xml('account', true, FALSE, '');
-----The table structure is as: CREATE TABLE public.account ( account_id INTEGER NOT NULL, name VARCHAR(1) NOT NULL, type VARCHAR(20), group_name VARCHAR(50), CONSTRAINT account_pkey PRIMARY KEY(account_id));
Question: How can I directly load data into account table using XML file in PostgreSQL?
I had to use varchar(2) due to the conversion from xml.
I've used a select into (creates public.account)
select account_id::text::int, account_name::varchar(2),
account_type::varchar(20) , account_group::varchar(50) INTO
public.account from(
WITH x AS ( SELECT
'<accounts>
<account>
<account_id>1</account_id>
<account_name> A </account_name>
<account_type> off shore</account_type>
<account_group> slush fund </account_group>
</account>
<account>
<account_id>3</account_id>
<account_name> C </account_name>
<account_type> off shore</account_type>
<account_group> slush fund </account_group>
</account>
</accounts> '::xml AS t)
SELECT unnest(xpath('/accounts/account/account_id/text()', t))
AS account_id,
unnest(xpath('/accounts/account/account_name/text()', t))
AS account_name,
unnest(xpath('/accounts/account/account_type/text()', t))
AS account_type,
unnest(xpath('/accounts/account/account_group/text()', t))
AS account_group
FROM x) as accounts
If you're interested in reading the xml file in then this may be useful.
Ref stackexchange sql to read xml from file into postgresql
I hope this helps
