HTTP GET - Download and Parse HTML

Downloads an HTML page from the Singapore Exchange and parses a row of options prices from an HTML table.

Download Chilkat XML ActiveX

Download Chilkat HTTP ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    --  Any string unlocks the component for the 1st 30-days.
    EXEC sp_OAMethod @http, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Send the HTTP GET and return the content in a string.
    DECLARE @html nvarchar(4000)

    EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'http://esite.sgx.com/live/dt/DTFuture.asp?JBFE'

    --  Use the Chilkat HTML-to-XML component to convert
    --  the HTML to parsable XML:
    DECLARE @htmlToXml int
    EXEC @hr = sp_OACreate 'Chilkat.HtmlToXml', @htmlToXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Any string argument automatically begins the 30-day trial.

    EXEC sp_OAMethod @htmlToXml, 'UnlockComponent', @success OUT, '30-day trial'
    IF @success <> 1
      BEGIN
        PRINT 'HtmlToXml component unlock failed'
        RETURN
      END

    --  Indicate the charset of the output XML we'll want.
    EXEC sp_OASetProperty @htmlToXml, 'XmlCharset', 'utf-8'

    --  Set the HTML:
    EXEC sp_OASetProperty @htmlToXml, 'Html', @html

    --  We won't need the scripts or images, so drop those tags...
    EXEC sp_OAMethod @htmlToXml, 'DropTagType', NULL, 'img'
    EXEC sp_OAMethod @htmlToXml, 'DropTagType', NULL, 'script'

    --  Get the XML:
    DECLARE @xmlStr nvarchar(4000)

    EXEC sp_OAMethod @htmlToXml, 'ToXml', @xmlStr OUT

    --  Load the XML into the Chilkat XML parser:
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @xml, 'LoadXml', NULL, @xmlStr

    --  success = xml.SaveXml("out.xml");

    DECLARE @node int

    --  Find a known point in the XML.  In this case we'll look for the text node
    --  containing this string: "Chg From Prev Settle"
    EXEC sp_OAMethod @xml, 'SearchForContent', @node OUT, NULL, 'text', 'Chg From Prev Settle'
    IF @node Is NULL 
      BEGIN
        PRINT 'Did not find Chg From Prev Settle'
        RETURN
      END

    --  Move up to the TD node:
    EXEC sp_OAMethod @node, 'GetParent2', NULL

    --  Move up to the TR node:
    EXEC sp_OAMethod @node, 'GetParent2', NULL

    --  Move to the next row (i.e. next TR)
    EXEC sp_OAMethod @node, 'NextSibling2', NULL

    EXEC sp_OAMethod @node, 'FirstChild2', NULL
    EXEC sp_OAMethod @node, 'NextSibling2', NULL


    EXEC sp_OAMethod @node, 'GetChildContent', @sTmp0 OUT, 'text'
    PRINT 'Expire Month/Year: ' + @sTmp0
    EXEC sp_OAMethod @node, 'NextSibling2', NULL


    EXEC sp_OAMethod @node, 'GetChildContent', @sTmp0 OUT, 'text'
    PRINT 'Last: ' + @sTmp0
    EXEC sp_OAMethod @node, 'NextSibling2', NULL


    EXEC sp_OAMethod @node, 'GetChildContent', @sTmp0 OUT, 'text'
    PRINT 'Change: ' + @sTmp0
    EXEC sp_OAMethod @node, 'NextSibling2', NULL

    --  ...

    EXEC sp_OAMethod @node, 'SaveXml', @success OUT, 'row.xml'


END
GO