CODEWITHSUNDEEP
plpgsql
MOnalee Bhandge
MOnalee Bhandge

Reputation: 21

How to import XML data into postgres database table?

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?

Upvotes: 0

Views: 5309

Answers (1)

user1123335
user1123335

Reputation:

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

Upvotes: 1

Related Questions