Solved

How to extract information from an XML field header

Posted on 2012-03-22
8
586 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Input box criteria 3 19
Exiting a Form/Subform does not work correctly 9 16
VBA Shell can't Find Word document 11 41
XSLT Help 12 19
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

14 Experts available now in Live!

Get 1:1 Help Now