How to pick data from web pages and insert into a sql table

Question

How to pick data from web pages and insert into a sql table

imneaz

Say Hey Kid

Points: 711
More actions
March 11, 2014 at 8:33 am

#305186

Dear, Experts,
I am very novice in SQL 2008 programming. I want to capture live cricket score from http://www.espncricinfo.com/ci/engine/current/match/scores/live.html. and insert into a column of SQL table. My expectation is when the website (Live Updates) is updated automatically records are inserted into my table (tbl_CricketUpdate). I have 3 columns. 1. UPDATE_ID, 2. LIVE_UPDATE_TEXT, 3. UPDATE_TIME.
UPDATE_ID column will update (auto increment) when new recodes inserted.
LIVE_UPDATE_TEXT column inserted from website (Live Updates) data
UPDATE_TIME takes date and times.
Please Expert Guys help me.
Thanks & kinds
imneaz

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

well i went to the link provided, and have no ida what data you want to capture; i'd guess it's the short blurbs ont he left of the page?

whatever that data is, you'd need to parse the html for it.

some rough guess on how to do it:

either via SSIS or a CLR procedure that reads a web page, you need to grab teh html.

parse the html into fields/columns/stories

inser that data into your database

schedule the process to run every x minutes in a job.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

imneaz Say Hey Kid Points: 711 More actions · Answer 2

Dear Lowell,

yes, I just learned and made a CLR procedure but how CLR procedure reads that web side? would you please show some code/example which read the web page.

another asking is how I parse html text/record into fields/columns/stories?

need to read website (Live Updates)

http://www.espncricinfo.com/ci/engine/current/match/scores/live.html

please experts show me a way.

thanks

inmeaz

Lowell SSC Guru Points: 323507 More actions · Answer 3

here's a simple example that returns the html as a varchar(max) I think; note it doesn't download images or anything else on the page.

'requires

'Imports System.Net

<Microsoft.SqlServer.Server.SqlFunction()>

Public Shared Function CLR_WebQuery(ByVal URL As String) As SqlChars ' SqlString = varchar(8000) , SqlChars=varchar(max)

Dim request As WebRequest = HttpWebRequest.Create(URL)

Using response As WebResponse = request.GetResponse()

Using dataStream As Stream = response.GetResponseStream()

Using reader As New StreamReader(dataStream)

Dim responseFromServer As String = reader.ReadToEnd()

Return New SqlChars(New SqlString(responseFromServer))

End Using

request = Nothing

End Function

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!