Import XML into Access (1 Viewer)

[NMadson]

First, thanks to Rx for posting code to import XML into Access. That code example got me further along than anything else I've found.

The data that I'm working with is stock options price data from Yahoo!Finance. (I'm using MS Access 2010.) The problem I have is that the second example code for XML without a style sheet only imports sub-child elements. Below are the first few lines of a file that I want to import.

I tried adding references to Microsoft XML v3.0, v4.0 & v6.0. None of them impacted the final result. Only sub-child elements were imported for each of these references.

It has been many years since I worked with XML and I'm completely unfamiliar with using Access to work with XML. Any pointers as to how to get the parent and child elements imported also would be much appreciated.

TIA,
Nolan

PS Since I'm a newbie I can't include the link the XML file includes. If this is relevant I can spell it out for any interested parties.

PPS Since I couldn't maintain formatting that would make the XML easy to read I've attached a screen shot of the first two records from the XML file.

NM
--------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
-<query yahoo:lang="en-US" yahoo:created="2013-12-11T15:13:05Z" yahoo:count="11" xmlns:yahoo="{link omitted as requested}">
<!-- total: 11093 -->

<!-- engine5.yql.gq1.yahoo.com -->
-<results>-<optionsChain symbol="SPY" expiration="2013-12-13">-<option symbol="SPY131221C00020000" type="C"><strikePrice>20</strikePrice><lastPrice>150.53</lastPrice><change>0</change><changeDir/><bid>160.15</bid><ask>160.39</ask><vol>10</vol><openInt>10</openInt></option>+<option symbol="SPY131221C00030000" type="C">-<option symbol="SPY131221C00035000" type="C"><strikePrice>35</strikePrice><lastPrice>132.90</lastPrice><change>0</change><changeDir/><bid>145.17</bid><ask>145.4</ask><vol>30</vol><openInt>70</openInt></option>

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=242332
Per your first reference, here is the code solution for those who may have the same question.

I will try to ask my XML Guru about this over the weekend. Thanks for the update about what worked and did not work.

 

[Galaxiom]

Access XML works with Element-centric XML

<lastPrice>150.53</lastPrice>

but not Attribute-centric XML

<optionsChain symbol="SPY" expiration="2013-12-13">

Your data is a mixture of the two.

sub-child elements were imported for each of these references.

This suggests the Element centric parts are being imported but the Attribute centric data is ignored.

I think you would need to parse the text to extract the attribute centric data.

 

[NMadson]

Galaxiom,

Thanks for the explanation.

Instead of parsing the attribute data, I think I'll use Excel to download the data, convert it into .xls and then import that spreadsheet into Access. Excel can handle the mix of attribute- and element-oriented XML. The project primarily uses Excel so the majority of the project is being written using Excel VBA. I just wanted to use Access to house the historic data, since this process will generate a lot of data.

Nolan

 

[mdlueck]

Nolan,

I had better success asking MSXML questions in the MS forum rather than here, even though I am using MSXML within Access VBA. Here is the link to that forum:

XML, System.Xml, MSXML and XmlLite Forum

Samples of my use of MS XML6 objects: