As I have blogged previously, during webDU conference Yahoo!’s YQL really caught my attention. I really think it is really cool how you can leverage your SQL and XPath knowledge to easily query any websites that you want.
I can think of at least one application of this technology. Imagine that you have a company that you’d like to work for. You go to the site, great it has a job section, but you don’t see any position that matches your profile. So you check again few weeks later and again and again. Well imagine if there are more than one companies that you want to keep watching – the manual process can be very tedious. Of course, if these companies provide RSS feeds on their jobs section, we can simply use our RSS aggregator to do our job, however many of these companies (if not most) don’t provide RSS feed on their jobs section.
And this is where I think YQL can be of help. You can simply create your own jobs aggregator for different sites using YQL.
In this example, I built a ColdFusion template to “watch” jobs openings on UNSW and Gruden. Well, actually the site that I would really to aggregate is the Whirlpool’s Job Board – but querying the site using YQL isn’t possible due to its robots.txt denies crawlers (Simon, give us an RSS feed, please :) ).
Below is the code: [coldfusion] <cfsilent> <cfset yahooURL = "http://query.yahooapis.com/v1/yql?"> <!— TODO: need to work out oAuth - the public URL will have to do for the moment —> <cfset yahooURL = "http://query.yahooapis.com/v1/public/yql?">
<cfset lCompanies = "UNSW,gruden">
<!--- START | Prepare the YQLs --->
<cfsavecontent variable="UNSWYQL">
SELECT * FROM html
WHERE
url IN (
"http://www.hr.unsw.edu.au/services/recruitment/newjobres.html"
, "http://www.hr.unsw.edu.au/services/recruitment/newjobgen.html"
, "http://www.hr.unsw.edu.au/services/recruitment/newjobaff.html"
)
AND xpath='//a[@class="iw_tst"]'
</cfsavecontent>
<!--- Cannot get data from whirlpool job board - crawl is restricted by robots.txt
<cfsavecontent variable="WHIRLPOOLYQL">
SELECT * FROM html
WHERE
url = "http://whirlpool.net.au/jobs/?state=NSW"
AND xpath='//div[@id="jobs"]/li[@class=""]'
</cfsavecontent>
--->
<cfsavecontent variable="grudenYQL">
SELECT * FROM html
WHERE
url = "http://gruden.com/index.cfm/p/jobs"
AND xpath='//div[@id="gspot"]/ul/li/a'
</cfsavecontent>
<!--- END | Prepare the YQLs --->
<!--- Run Query
1. Loop to call Yahoo! URL for each companies
2. Save the result in variable called #company#Result ie: UNSWResult
--->
<cfloop list="#lCompanies#" index="company">
<cfset theYQL = Evaluate( company & "YQL" )>
<cfset theURL = "#yahooURL#q=#Trim( theYQL )#">
<cfhttp
url = "#theURL#"
result = "st#company#Result">
</cfhttp>
<cfset variables[ "#company#Result" ] = variables[ "st#company#Result" ].fileContent>
</cfloop>
<!--- Transform the data ( good place to use XSLT here )--->
<!--- UNSW --->
<cfset UNSWXML = XMLParse( UNSWResult )>
<cfset arrUNSWResult = XMLSearch( UNSWXML, "//results/a" )>
<!--- Gruden --->
<cfset grudenXML = XMLParse( grudenResult )>
<cfset arrGrudenResult = XMLSearch( grudenXML, "//results/a" )>
</cfsilent> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Yahoo! Query Language Demo using the all powerful ColdFusion</title> </head>
<body> <cfoutput> <h1>YQL - It’s like SELECT * FROM THE INTERNET DUDE!</h1>
<h2>UNSW</h2>
<p>
<ul>
<cfloop from="1" to="#ArrayLen( arrUNSWResult )#" index="index">
<cfset jobTitle = arrUNSWResult[ index ][ "XMLText" ]>
<cfset jobLink = arrUNSWResult[ index ][ "XMLAttributes" ][ "href" ]>
<li><a href="http://www.hr.unsw.edu.au/#jobLink#">#jobTitle#</a></li>
</cfloop>
</ul>
</p>
<h2>Gruden</h2>
<p>
<ul>
<cfloop from="1" to="#ArrayLen( arrGrudenResult )#" index="index">
<cfset jobTitle = arrGrudenResult[ index ][ "XMLText" ]>
<cfset jobLink = arrGrudenResult[ index ][ "XMLAttributes" ][ "href" ]>
<li><a href="http://gruden.com/index.cfm/p/jobs#jobLink#">#jobTitle#</a></li>
</cfloop>
</ul>
</p>
</cfoutput> </body> </html>
[/coldfusion] And here is the snapshot of the result:
For some reasons the code doesn’t work on Railo though – I got “The markup in the document preceding the root element must be well-formed.” error on the XMLParse bit.. oh well that’s thought for another day.