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

There was dblink contrib module which reads data from other PostgreSql
database using tcp/ip connection.
Maybe there is similar for http/asmx data retrieval ?

About periodic call of stored procedure, is there cron contrib for PosgreSql
? Or can we force some code call on autofacuum or after
every server request like poor man crontab in drupal/php? This code can
check and call refresh on evry hour.

Can we add some trigger code for some frequently used table or view to
implement poor man crontab ?

Can some serve sider langeage used for those ?
Is server side C#/.NET,Mono already implemented ?

Andrus.



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

I think this approach is much better as you can solve everything on
server itself.

About your question on http request I don't know.
Sorry for that. :(
Maybe there is a module for Postgresql which can enable you to make http
calls?



On Sat, Oct 8, 2011 at 17:15, Andrus <kobruleht2@hot.ee> wrote:
> 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
>



--
Regards,

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