Solved

How to extract information from an XML field header

Posted on 2012-03-22
8
642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

739 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