Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Importing XML into Access?

    First, Merry Christmas to those who celebrate it!

    Today, millions of sites use XML as a convient way of storing, presenting and cross-platform exchange of data.

    You have acces to many valuable information via XML feeds.

    However, Access does a pathetic job of importing XML files in meaningful tables, Excel is slightly better.

    SO, MY QUESTION IS: WHAT IS THE BEST TOOL TO GET XML FEEDS INTO DATABASE FORMAT (obviously not Access)?

    I tried searching the forums but XML put into search field returns exactly 0 results accross all forums.

    Below is the example of what I mean by converting XML to meaningful tables. You have a short example of sports odds XML feed provided by pinnaclesports.com which they offer freely.

    - <event>
    <event_datetimeGMT>2007-12-26 12:59</event_datetimeGMT>
    <gamenumber>113708073</gamenumber>
    <sporttype>Soccer</sporttype>
    <league>Eng. Premier</league>
    - <participants>
    - <participant>
    <participant_name>West Ham United</participant_name>
    <contestantnum>907</contestantnum>
    <rotnum>907</rotnum>
    <visiting_home_draw>Home</visiting_home_draw>
    </participant>
    - <participant>
    <participant_name>Reading</participant_name>
    <contestantnum>908</contestantnum>
    <rotnum>908</rotnum>
    <visiting_home_draw>Visiting</visiting_home_draw>
    </participant>
    - <participant>
    <participant_name>Draw</participant_name>
    <rotnum>909</rotnum>
    <contestantnum>909</contestantnum>
    <visiting_home_draw>Draw</visiting_home_draw>
    </participant>
    </participants>
    - <periods>
    - <period>
    <periodnumber>0</periodnumber>
    <period_description>Game</period_description>
    <periodcutoff_datetimeGMT>2007-12-26 12:59</periodcutoff_datetimeGMT>
    <period_status>I</period_status>
    - <moneyline>
    <moneyline_home>-113</moneyline_home>
    <moneyline_visiting>340</moneyline_visiting>
    <moneyline_draw>240</moneyline_draw>
    </moneyline>
    - <spread>
    <spread_visiting>0.5</spread_visiting>
    <spread_adjust_visiting>101</spread_adjust_visiting>
    <spread_home>-0.5</spread_home>
    <spread_adjust_home>-109</spread_adjust_home>
    </spread>
    - <total>
    <total_points>2.5</total_points>
    <over_adjust>102</over_adjust>
    <under_adjust>-112</under_adjust>
    </total>
    </period>
    </periods>
    </event>

    As you can see you have an event which is Eng. Premier league game, followed by participants West Ham and Reading followed by periods and betting options!

    If you try to import that into Access you'll get pathetically fragmented tables, called events, participants, periods etc. with no link between them.

    If you wanted meaningful import you'd want to get Event No ....#####.... into Events table with one-to-many realtionship to participants, periods, betting options and so on. (Naturally events can have from 2 to many paricipants, US sports have 2, soccer has 3 - draw added, horse races and Who wins NBA 2008 have many etc., periods vary from 1 to many - example betting on 1st third, game without overtime, game including overtime in NHL.)

    SO, IS THERE A WAY TO GETTING XML DATA INTO ANY DATABASE IN A CORRECT OR USER-DEFINED FASHION?

    If Access doesn't have that option (and it appears it doesn't) then please point me in the right direction!

    I think it's an interesting topic.

    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    108
    Nice try georgev! Been there.

    But, it provides general info on where to find Import external data in Access which results in catastrophic and useless imports described in my original post, so I was looking for something specific!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SQL Server 2005 Express edition would be my next port of call
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    108
    OK, to illustrate in simple example how Access fails to import XML correctly you can copy two examples below, save them as .xml files and try importing them into Access.

    elements.xml

    Code:
    <List_of_customers>
    <Customer>
    <ID>5</ID>
    <Name>John</Name>
    <LastName>Johnson</LastName>
    </Customer>
    <Customer>
    <ID>6</ID>
    <Name>Linda</Name>
    <LastName>McCarthy</LastName>
    </Customer>
    </List_of_customers>
    attributes.xml

    Code:
    <List_of_customers>
    <Customer ID="5"
    	Name="John">
    <LastName>Johnson</LastName>
    </Customer>
    <Customer ID="6"
    	Name="Linda">
    <LastName>McCarthy</LastName>
    </Customer>
    </List_of_customers>
    If you try importing the first file elements.xml you'll get satisfactory result because Customer is used as element-only XML element, meaning it contains only other elements which represent columns/fields in a table and Customer being element-only XML element represents row.

    However if you try importing second file attributes.xml you'll get c..p, you'll get one table with one field LastName. That is because data values are incorporated in Customer via using attributes of Customer XML element which Access simply ignores.

    Real life XML is usually mix of both approaches!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    This is why I keep saying that XML is an unstable "language". It needs a strict structure implemented so that these kinds of problems can be dealt with. It's also why I tend to dodge it as much as I can. I believe it will be replaced by something more structured... or it will just evolve.

    Perhaps I am missing something too?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Riorin
    OK, to illustrate in simple example how Access fails to import XML correctly you can copy two examples below, save them as .xml files and try importing them into Access.

    elements.xml

    Code:
    <List_of_customers>
    <Customer>
    <ID>5</ID>
    <Name>John</Name>
    <LastName>Johnson</LastName>
    </Customer>
    <Customer>
    <ID>6</ID>
    <Name>Linda</Name>
    <LastName>McCarthy</LastName>
    </Customer>
    </List_of_customers>
    attributes.xml

    Code:
    <List_of_customers>
    <Customer ID="5"
    	Name="John">
    <LastName>Johnson</LastName>
    </Customer>
    <Customer ID="6"
    	Name="Linda">
    <LastName>McCarthy</LastName>
    </Customer>
    </List_of_customers>
    If you try importing the first file elements.xml you'll get satisfactory result because Customer is used as element-only XML element, meaning it contains only other elements which represent columns/fields in a table and Customer being element-only XML element represents row.

    However if you try importing second file attributes.xml you'll get c..p, you'll get one table with one field LastName. That is because data values are incorporated in Customer via using attributes of Customer XML element which Access simply ignores.

    Real life XML is usually mix of both approaches!
    As I understand it you cannot use spaces in tags
    so the tag of "Customer ID" in version 2 is invalid
    it may be that Access trying to get a soft landing is allowing that. but in strict XML you wont get away with it.
    theoretically you should supply a DTD with the XML to precisely tie down the tabs, datatypes etc.

    what happens if you try to load both XML files into another XML aware application, if so what happens

    lastly if you are loading this into Access, and you are getting indeterminate result then you could always go and write your file handler. After all XML files are text files, granted you may have to use an extended character set to handle non ASCII characters, but again that should have been determined in the first line of the XML file, or the DTD.

    you talk of the best tool to get data in XML format... well Access is perfectly acceptable as a mechanism for generating XML data
    you can do it as part of a query by encapsulating each relevent column in the appropriate tag, you may need to include a vbcrlf or lf, asci lf to separate lines
    you could do it by writing to a file.
    as, I think it was Izzy said, in an earlier post of yours Access can do just about anything you want.. I'd add:
    providing you know what you want,
    providing you are prepared to go through the hoops it demands of you (as all languages do)
    Access is ultimately extensible if you write your won classes, functions and VB code
    plain vanilla Access wont, but Access is a chameleon, it can pretend to be virtually anything to anyone the person driving Access is prepared to push it that way. Obviously it ain't going to work on embedded machines, it aint going to work on time critical code, but if its a db application or interface then it has a heck of a lot going for it. Its one of the first weapons that I'd choose in the daily combat of meeting user requirements. Sometimes it isnt' appropriate, but often it does the job
    Last edited by healdem; 12-27-07 at 12:56.

  8. #8
    Join Date
    Apr 2007
    Posts
    108
    As I understand it you cannot use spaces in tags
    so the tag of "Customer ID" in version 2 is invalid
    it may be that Access trying to get a soft landing is allowing that. but in strict XML you wont get away with it.
    Wrong, Customer is tag, ID is attribute and it's perfectly legal XML, take a look at www school XML.


    you talk of the best tool to get data in XML format... well Access is perfectly acceptable as a mechanism for generating XML data
    I wasn't thinking of exporting data from Access but importing XML into Access without having to write much, I understand XML is plain text file and I can write my own parser but why bother if the solution is out there?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    God I hate XML...
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup. XML is a nice idea, but it's not achieving what it was intended to.

    I'd hate to hear that Riorin would have to write his own parser
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Apr 2007
    Posts
    108
    I was just trying to find out if a valuable tool for importing XML files into relational databases existed - guess the answer is NO.

    That is both disappointing - given that you find solutions to almost anything today and you are never the first one to stumble into something new and surprising because Internet today is loaded with XML and you'd expect a tool to be out there to put that XML into database.

    The tool could pull out all XML elements and attributes, ask you what element and what attribute belongs to what tables, are there any relationships and so on, on places where one element like <event> contains more <participants> it would assume 1:M relationship etc.

    Maybe I should make one?

    After all, XML is well-formatted with strict syntax, case-sensitive, requires proper nesting etc.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what happens if you import your XML file into prexisting tables which have the relationships, datatypes etc all predetermined, rather than importing into new tables.

    The alternative is to write a VBA process which loads the XML data into memory and then writes that to the appropriate tables. You may need to do this to handle duplicates, RI constraint fails etc..

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just because we don't know where to easily get one, it doesn't mean that it doesn't exist.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Apr 2007
    Posts
    108
    Trying to import XML into preexisting tables fails, because Access ignores values stored in attributes completely (second Customer example) and provides no means to represent 1:M relationship (implemented in XML via elements containing other elements - like in Pinnacle XML feed, event element contains two or more participants) and link them correctly via primary and foreign key (in Pinnacle XML feed that would be event ID number).

    Anyway, the whole point of my original post was to bring up the discussion of a common problem - not to solve this particular problem.

    This particular example - harvesting Pinnacle XML odds can be done by using Excel (which extracts all XML data including attribute values) as intermediate and later you can import Excel spreadsheets and with few queries split the spreadsheet into properly formed tables - Events and Participants with correct 1:M realtionship.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •