Solved

How to extract information from an XML field header

Posted on 2012-03-22
8
601 Views
Last Modified: 2012-04-02
Good morning.  I apologize if I used incorrect terminology in my title but I'm still learning...I'll get better ;-)

I'm learning how to work with XML data in my Access 2010 database and I'm making pretty good progress, but I can't find any information about how to do this:

I'm using the ISBNDB.com books database as my source.  When I enter a particular book number (ISBN), I receive something like the following snippet (for some reason the code snippet tool is greyed out in my browser so I have to insert the unformated text here):

<?xml version="1.0" encoding="UTF-8"?>
<ISBNdb server_time="2012-03-22T16:55:40Z">
<BookList shown_results="1" page_number="1" page_size="10" total_results="1">
<BookData isbn13="9780451205063" isbn="0451205065" book_id="ralph_compton_runaway_stage">
<Title>Ralph Compton Runaway Stage</Title>
<TitleLong>Ralph Compton Runaway Stage (Ralph Compton Novels (Paperback))</TitleLong>
<AuthorsText>Ralph Compton, Robert Vaughan, </AuthorsText>
<PublisherText publisher_id="signet">Signet</PublisherText>
<Details language="" price_time="2012-03-22T02:10:51Z" dewey_decimal="813.54" dewey_decimal_normalized="813.54" lcc_number="" physical_description_text="288 pages" edition_info="Paperback; 2002-01-01" change_time="2007-01-19T14:27:35Z"/> </BookData>
</BookList>
</ISBNdb>

I've learned how to extract the text from things like <Title> and other fields, but I'm particularly interested in being able to extract portions of field "headers" (again, I don't know if that's the right term or not) like <Details>.  In particular, I'd like to get the date portion of the "edition_info" section of the header.  I know how to parse strings to extract the date - that's no problem.  I just don't know how to write the VBA code to extract the "edition_info" from the <Details> header.

FYI...I'm already familiar enough with how to work with XML in Access 2010 to write a working function so I don't need a whole procedure - just an example of how to get that particular piece of data from the XML text.  Thanks!
0
Comment
Question by:penlandt
  • 5
  • 3
8 Comments
 
LVL 57
ID: 37753812
<<FYI...I'm already familiar enough with how to work with XML in Access 2010 to write a working function so I don't need a whole procedure - just an example of how to get that particular piece of data from the XML text.  >>

 Well you might be in for a bit of surpise; have you worked with the XML Document object?  or is everthing you've done been based on string parsing?

Jim.
0
 
LVL 1

Author Comment

by:penlandt
ID: 37754157
Yes, I currently have a function that pulls the title, author, publisher, etc. from the document object.  But those strings all lie between the tags, i.e.:

<author>Author Name</author>
<title>Title</title>
<publisher>Publisher</>

The <detail> section in my original example is a little different.  It is formed like this:

<detail edition_info="Paperback; 2002-01-01"></detail>

Does that help clarify the question?  Thanks!
0
 
LVL 1

Author Comment

by:penlandt
ID: 37754321
By the way, here's the function I mentioned (not mine, but modified from someone else's example):

Public Function Lookup(ISBN As String) As Boolean
    Lookup = False
    Dim xmlhttp
    Set xmlhttp = CreateObject("MSXML2.xmlhttp")
    xmlhttp.Open "GET", "https://isbndb.com/api/books.xml?access_key=" & accessKey & "&results=texts&index1=isbn&value1=" & ISBN, False
    xmlhttp.send
    'Debug.Print "Response: " & xmlhttp.responseXML.XML'
    Dim xmldoc
    Set xmldoc = CreateObject("Microsoft.XMLDOM")
    xmldoc.async = False
    'Note: the ResponseXml property parses the server's response, responsetext doesn't
    xmldoc.loadXML (xmlhttp.responseXML.XML)
    If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") = 0) Then
        MsgBox "Invalid ISBN or not in database"
        Exit Function
    End If
    If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") > 1) Then
        MsgBox "Caution, got more than one result!"
        Exit Function
    End If
    BookTitle = xmldoc.selectSingleNode("//BookData/Title").Text
    BookTitleLong = xmldoc.selectSingleNode("//BookData/TitleLong").Text
    BookAuthorsText = xmldoc.selectSingleNode("//BookData/AuthorsText").Text
    BookPublisherText = xmldoc.selectSingleNode("//BookData/PublisherText").Text
    BookNotes = xmldoc.selectSingleNode("//BookData/Notes").Text
    BookSummary = xmldoc.selectSingleNode("//BookData/Summary").Text
    BookUrlsText = xmldoc.selectSingleNode("//BookData/UrlsText").Text
    BookAwardsText = xmldoc.selectSingleNode("//BookData/AwardsText").Text
    Lookup = True
End Function
0
 
LVL 57
ID: 37754426
<<Yes, I currently have a function that pulls the title, author, publisher, etc. from the document object.>>

Great.  You should be able to use:

xmldoc.getElementsByTagName("author")[0].childNodes

If I'm looking at this right.

BTW, a very nice tutorial on XML and all the objects, methods, and what not can be found here:

http://w3schools.com/dom/default.asp

Jim.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:penlandt
ID: 37754642
Thanks for the tutorial link.  That will be helpful.  I'm a little confused though: you referred to the "authors" element in your solution but it was one of the attributes (I believe that's the right term) of the "details" element that I'm trying to reference.  So would the correct command be:

xmldoc.getElementsByTagName("details")[0].childNodes

Is that right?  Thanks again.
0
 
LVL 57
ID: 37754752
<<Is that right?  Thanks again.>>

  yeah, sorry I grabbed the wrong thing from your comment.

There maybe a better way to get it, which is why I included the W3C link.  It's a great site because you can actually try things out right there in the window.

 But I hadn't seen the selectSingleNode() used before (I'm OK at XML, but not fantastic), so I'm not sure I'm looking at it right.

  If it doesn't do it for you let me know and I'll figure it out.

Jim.
0
 
LVL 1

Accepted Solution

by:
penlandt earned 0 total points
ID: 37778067
Feeling kinda silly right now.  My sample code actually had the answer to my question in it.  The way you get attribute information is this (copied from my sample code):

xmldoc.selectSingleNode("//BookList").getAttribute("total_results") = 0

Sorry about that.  I'm learning.
0
 
LVL 1

Author Closing Comment

by:penlandt
ID: 37795145
Already explained in my last post.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now