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 <kobrule...@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 <eetas...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
