(SQL Server) Fetch ISBN XML from isbndb.com and Parse

Demonstrates sending a query to isbndb.com and parsing the XML response.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- The access_key in this URL is a valid access key, but limited to 100 queries per day.
    -- If the key does not work, it may be that others were testing and exhausted the limit.
    DECLARE @strXml nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @strXml OUT, 'http://isbndb.com/api/books.xml?access_key=OV8L2Y9I&results=details&index1=isbn&value1=0443074348'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    DECLARE @success int
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @strXml

    -- The XML returned for this URL is the following:

    -- <?xml version="1.0" encoding="UTF-8" ?>

    -- <ISBNdb server_time="2007-10-25T14:07:25Z">
    --     <BookList total_results="1" page_size="10" page_number="1" shown_results="1">
    --         <BookData book_id="diagnostic_histopathology_of_tumors_2_volume_set_with_cd_rom" isbn="0443074348">
    --             <Title>Diagnostic Histopathology of Tumors: 2-Volume Set with CD-ROMs</Title>
    --             <TitleLong>Diagnostic Histopathology of Tumors: 2-Volume Set with CD-ROMs (Diagnostic Histopathology of Tumors (Fletcher))</TitleLong>
    --             <AuthorsText>Christopher D. M. Fletcher (Editor)</AuthorsText>
    --             <PublisherText publisher_id="churchill_livingstone">Churchill Livingstone</PublisherText>
    --             <Details dewey_decimal="616" physical_description_text="2166 pages" language="" edition_info="Hardcover; 2007-03-13" dewey_decimal_normalized="616" lcc_number="" change_time="2006-12-13T14:53:42Z" price_time="2007-10-25T01:12:13Z" />
    --         </BookData>
    --     </BookList>
    -- </ISBNdb>

    -- First, navigate to the BookData node:
    EXEC sp_OAMethod @xml, 'FirstChild2', @success OUT
    EXEC sp_OAMethod @xml, 'FirstChild2', @success OUT

    -- Show the Title and AuthorsText:
    EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'Title'
    PRINT @sTmp0
    EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'AuthorsText'
    PRINT @sTmp0

    -- Show the publisher_id attribute of the PublisherText node:
    DECLARE @xml2 int

    EXEC sp_OAMethod @xml, 'FindChild', @xml2 OUT, 'PublisherText'
    EXEC sp_OAMethod @xml2, 'GetAttrValue', @sTmp0 OUT, 'publisher_id'
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xml2

    -- Save the XML to a file:
    EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'book.xml'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @xml


END
GO