Solved

How to extract information from an XML field header

Posted on 2012-03-22
8
625 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 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