Re: How to add xml data to table - Mailing list pgsql-general

Thank you.
I got also the following code:

First import the XML into a staging table:

CREATE TABLE xml_import
(
   xml_data  xml
)

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

Looks simpler than using XmlReader, isn't it?
How to invoke asmx web service call (= http POST request) from
PostgreSql server  which reads http response to xml_import  table ?

How to call stored procedure periodically after every one hour in server?

In this case we can create stored procedure, client side code is not
nessecary at
all.

Andrus.

-----Algsõnum-----
From: Francisco Figueiredo Jr.
Sent: Saturday, October 08, 2011 9:38 PM
To: Andrus Moor
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to add xml data to table

I think your best bet would be to go with XmlReader as it provides a
fast read only parsing of the document.

From MS doc about linq to xml:
http://msdn.microsoft.com/en-us/library/bb387048.aspx

"XmlReader is a fast, forward-only, non-caching parser.

LINQ to XML is implemented on top of XmlReader, and they are tightly
integrated. However, you can also use XmlReader by itself.

For example, suppose you are building a Web service that will parse
hundreds of XML documents per second, and the documents have the same
structure, meaning that you only have to write one implementation of
the code to parse the XML. In this case, you would probably want to
use XmlReader by itself.

In contrast, if you are building a system that parses many smaller XML
documents, and each one is different, you would want to take advantage
of the productivity improvements that LINQ to XML provides."


I think your case fits the first example.

This way you could use xmlreader to extract the values and then fill
NpgsqlParameter values and execute the insert command.

I hope it helps.



2011/10/7 Andrus Moor <eetasoft@online.ee>:
> 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 xpath() function or any other ides ?
> Using npgsql and C# in ASP .NET / Mono.
>
> Andrus.
>
>
> 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)
> )
>
> Data 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>



--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior