how to add xml data to postgresql database

Question

soap response below contains table of products, approx 5000 rows. Table of products (below) is nearly similar structure as xml data.

Products table needs to be updated from xml data in every hour.

How to add this xml data to table of products ?

Should I use PostgreSql xpath() function or any other ides ? Using npgsql and C# in ASP .NET / Mono.

CREATE TABLE products (
SupplierCode char(20) primary key,
SegmentId char(8),
GroupId char(8),
ClassId char(8),
SeriesId char(8),
VendorId char(2),
PartNumbrt char(27),
Name Text,
Warranty Numeric(6,2),
Price Numeric(10,4),
Quantity Numeric(8,2)
)

soap response which is required to add looks like:

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:xsd="http://www.w3.org/2001/XMLSchema"; xmlns:soap12="http://www.w3.org/2003/05/soapenvelope";>
<soap12:Body>
<GetProductListResponse xmlns="http://xxx.yy.zz/";>
<GetProductListResult>
<ProductList>
<Product>
<SupplierCode>001982</SupplierCode>
<SegmentId>65000000</SegmentId>
<GroupId>65010000</GroupId>
<ClassId>65010200</ClassId>
<SeriesId>10001125</SeriesId>
<VendorId>AM</VendorId>
<PartNumber>ADA3000BIBOX</PartNumber>
<Name>AMD Athlon64 3000+ (1800MHz/L2 Cache 512KB) Socket 939, BOX</Name>
<Warranty>36</Warranty>
<Price>196.00000</Price>
<Quantity>0</Quantity>
<DateExpected>1999-01-01T00:00:00</DateExpected>
<IsNewProduct>true</IsNewProduct>
</Product>
<Product>
<SupplierCode>001512</SupplierCode>
<SegmentId>65000000</SegmentId>
<GroupId>65010000</GroupId>
<ClassId>65010200</ClassId>
<SeriesId>10001125</SeriesId>
<VendorId>AM</VendorId>
Acme API Specification v 1.0
13
<PartNumber>ADA3000AXBOX</PartNumber>
<Name>AMD Athlon64 3000+ (2000MHz/1600MHz/L2 Cache 512KB) Socket 754, BOX</Name>
<Warranty>36</Warranty>
<Price>296.00000</Price>
<Quantity>0</Quantity>
<GrossWeight>3.6000</GrossWeight>
<DateExpected>1999-01-01T00:00:00</DateExpected>
<IsNewProduct>false</IsNewProduct>
</Product>
</ProductList>
</GetProductListResult>
</GetProductListResponse>
</soap12:Body>
</soap12:Envelope>

Closely related: http://stackoverflow.com/questions/7491479/xml-data-to-postgresql-database — Erwin Brandstetter, Feb 07 '13 at 01:52

score 1 · Accepted Answer · answered Oct 08 '11 at 15:27

To make things easier, I would first import the XML into a staging table:

CREATE TABLE xml_import
(
   xml_data  xml
)

Then once you have populated the staging table, you can retrieve the data from it using a SQL statement, converting the XML to a relational representation:

with product_list as (
  select unnest(xpath('/soap12:Envelope/soap12:Body/pl:GetProductListResponse/pl:GetProductListResult/pl:ProductList/pl:Product', xml_data, 
          ARRAY[ array['xsd', 'http://www.w3.org/2001/XMLSchema-instance'], 
                 array['soap12', 'http://www.w3.org/2003/05/soapenvelope'], 
                 array['pl', 'http://xxx.yy.zz/']])) as product
  from xml_import
)
select (xpath('/Product/SupplierCode/text()', product)::varchar[])[1] as suppliercode, 
       (xpath('/Product/SegmentId/text()', product)::varchar[])[1] as segmentid,
       (xpath('/Product/PartNumber/text()', product)::varchar[])[1] as partnumber,
       to_number((xpath('/Product/Price/text()', product)::varchar[])[1], '99999.99999') as price,
       to_number((xpath('/Product/GrossWeight/text()', product)::varchar[])[1], '9999.9999') as weight
from product_list

I didn't bother to include all columns, but I guess you get the picture.

I would put the above statement into a view, and then you can simply use the view to populate your real product table.

If you don't want to create that staging table, you probably can put everything into a single statement:

with xml_import (xml_data) as ( 
   select '.... xml goes here '::xml
), 
product_list as (
  ... same as above ...
)
select ... same as above ...
from product_list

how to add xml data to postgresql database

1 Answers1