Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 680
  • Last Modified:

How to extract information from an XML field header

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
penlandt
Asked:
penlandt
  • 5
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
penlandtAuthor Commented:
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
 
penlandtAuthor Commented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
penlandtAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
penlandtAuthor Commented:
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
 
penlandtAuthor Commented:
Already explained in my last post.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now