Fetch ISBN XML from isbndb.com and Parse

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

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

    --  The Chilkat XML component/class is freeware.

    DECLARE @xml int

    EXEC sp_OAMethod @xml0, 'HttpGet', @xml OUT, 'http://isbndb.com/api/books.xml?access_key=XXXXX&results=details&index1=isbn&value1=0443074348'
    IF @xml Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @xml0, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  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', NULL
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL

    --  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

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


END
GO