I need to download UN Blacklist XML file periodically. Is there any way to automate this using sql ONLY? |
The following function will return XML as a string: CREATE FUNCTION "DBA"."UNBlacklist"() returns long varchar url 'http://www.un.org/sc/committees/1267/consolidatedlist.xml' type 'HTTP:GET' ; To extract stuff from it, use select ... from OpenXML(). @Dmitri, thank you. Any comment on the usage of the "proxy" keyword? Is it possible to pass the network username/password authentication? Never tried it before, but adding proxy 'http://192.168.0.1:3128' seems to work. For the authentication, try adding user:password before the URL, e.g.: CREATE FUNCTION "DBA"."UNBlacklist"() returns long varchar url 'http://proxyuser:proxypwd@www.un.org/sc/committees/1267/consolidatedlist.xml' type 'HTTP:GET' proxy 'http://proxyserver:proxyport' ; On a second thought, 1) How to pass website authentication info? 2) How to pass network proxy authentication info? I think the following format will work, e.g: CREATE FUNCTION "DBA"."UNBlacklist"() returns long varchar url 'forumuser:forumpwd@www.someforum.com' type 'HTTP:GET' proxy 'networkuser:networkpwd@proxyserver:proxyport'; I dont know whether this will work. No need of this in the sample case above. I will try to download something which requires a web-login. |
Just as a quick start: You should be able to download any web document with SA's web client procedures/functions. I haven't done that myself so I can't give exact advice but you may have a look at the topics "SQL Anywhere web services" and "Web service clients", e.g. the following topic. Some ideas:
Just my thoughts 1
@Kumar: In addition to what Volker said, Eric Farrar might be able to help... he's the Go To Guy for a lot of SQL Anywhere topics including everything web-related. His blog is at http://iablog.sybase.com/efarrar/ ...alas, I am having a senior moment, can't find the perfect link for your needs. 1
@Kumar: Another resource is Deanne Chance who has written some articles on the subject for (yuk) sys-on.com. See "Consuming Amazon Web Services Using PowerBuilder and SQL Anywhere 10.0.1" (PowerBuilder only plays a small part) at http://dchance.sys-con.com/node/480658/mobile |