Import XML into Table

S

SikSlk

Registered User.
Local time
Tomorrow, 03:03
Joined
Aug 30, 2006
Messages
39
Im trying to import an XML file and having a terrible time trying to find information on it. Even got a book which hasn't yet got me on the right path as it was trying to explain exporting more then the importing.

I have found some info on here from gemma-the-huscky which i was fiddling with but cant even get a working example going at the moment.

I hate posting as I'm very confident in searching and through trail and error getting to where I need to be but this time im almost at a point of moving onto something else. :confused::confused::confused::confused:

Anyway, so far I have the xml file:
Code: 
<?xml version="1.0" encoding="UTF-8"?>
<eveapi version="2">
  <currentTime>2009-04-04 13:11:23</currentTime>
  <result>
    <rowset name="assets" key="itemID" columns="itemID,locationID,typeID,quantity,flag,singleton">
      <row itemID="436090243" locationID="66011888" typeID="27" quantity="1" flag="4" singleton="1">
        <rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton">
          <row itemID="266546894" typeID="2454" quantity="5" flag="116" singleton="0" />
          <row itemID="501426852" typeID="499" quantity="1" flag="117" singleton="0" />
          <row itemID="501426856" typeID="529" quantity="1" flag="117" singleton="0" />
          <row itemID="501426927" typeID="5321" quantity="1" flag="117" singleton="0" />
          <row itemID="501426968" typeID="7411" quantity="1" flag="117" singleton="0" />
          <row itemID="501426969" typeID="5049" quantity="1" flag="117" singleton="0" />
          <row itemID="502285232" typeID="267" quantity="200" flag="116" singleton="0" />
          <row itemID="502285353" typeID="11287" quantity="10" flag="116" singleton="0" />
          <row itemID="502918288" typeID="8091" quantity="1" flag="117" singleton="0" />
          <row itemID="502918375" typeID="3552" quantity="10" flag="116" singleton="0" />
          <row itemID="506029649" typeID="1977" quantity="1" flag="117" singleton="0" />
          <row itemID="506029791" typeID="2183" quantity="1" flag="116" singleton="0" />
          <row itemID="506029792" typeID="206" quantity="100" flag="116" singleton="0" />
          <row itemID="506675826" typeID="1201" quantity="3" flag="116" singleton="0" />
          <row itemID="506675827" typeID="1828" quantity="300" flag="116" singleton="0" />
          <row itemID="507285298" typeID="3981" quantity="3" flag="116" singleton="0" />
          <row itemID="507285564" typeID="443" quantity="1" flag="117" singleton="0" />
          <row itemID="507286366" typeID="3538" quantity="2" flag="117" singleton="0" />
          <row itemID="507286424" typeID="11279" quantity="1" flag="117" singleton="0" />
          <row itemID="507963976" typeID="2476" quantity="1" flag="116" singleton="0" />
          <row itemID="507963977" typeID="236" quantity="200" flag="116" singleton="0" />
          <row itemID="508703416" typeID="1832" quantity="300" flag="116" singleton="0" />
          <row itemID="508704205" typeID="10840" quantity="1" flag="117" singleton="0" />
          <row itemID="508704228" typeID="5047" quantity="1" flag="117" singleton="0" />
          <row itemID="508704343" typeID="3576" quantity="1" flag="117" singleton="0" />
          <row itemID="508704347" typeID="232" quantity="200" flag="116" singleton="0" />
          <row itemID="578056094" typeID="11359" quantity="1" flag="117" singleton="0" />
          <row itemID="578073036" typeID="260" quantity="1" flag="116" singleton="0" />
          <row itemID="585255205" typeID="575" quantity="1" flag="117" singleton="0" />
          <row itemID="667769842" typeID="235" quantity="100" flag="116" singleton="0" />
          <row itemID="746897415" typeID="2020" quantity="1" flag="117" singleton="0" />
          <row itemID="1318741361" typeID="236" quantity="2000" flag="116" singleton="0" />
          <row itemID="1319961468" typeID="3538" quantity="1" flag="117" singleton="0" />
          <row itemID="1468483633" typeID="3512" quantity="1" flag="119" singleton="1" />
          <row itemID="1567467013" typeID="593" quantity="1" flag="4" singleton="0" />
          <row itemID="1968549205" typeID="264" quantity="20" flag="116" singleton="0" />
          <row itemID="1692891568" typeID="2507" quantity="1" flag="121" singleton="1" />
          <row itemID="1692891566" typeID="2507" quantity="1" flag="121" singleton="1" />
          <row itemID="1572952537" typeID="444" quantity="1" flag="117" singleton="1" />
          <row itemID="1512238552" typeID="592" quantity="3" flag="4" singleton="0" />
          <row itemID="1968949157" typeID="573" quantity="2" flag="117" singleton="0" />
          <row itemID="1968949182" typeID="2020" quantity="3" flag="117" singleton="0" />
          <row itemID="1968949198" typeID="1832" quantity="100" flag="116" singleton="0" />
          <row itemID="1968949211" typeID="3887" quantity="4" flag="117" singleton="0" />
          <row itemID="1968949242" typeID="9660" quantity="1" flag="117" singleton="0" />
          <row itemID="1968949350" typeID="20238" quantity="1" flag="117" singleton="0" />
          <row itemID="1968949371" typeID="209" quantity="300" flag="116" singleton="0" />
          <row itemID="1968949436" typeID="10690" quantity="1" flag="117" singleton="0" />
          <row itemID="1968949437" typeID="8025" quantity="1" flag="117" singleton="0" />
          <row itemID="1968949490" typeID="2018" quantity="8" flag="117" singleton="0" />
          <row itemID="1968949508" typeID="3554" quantity="60" flag="116" singleton="0" />
          <row itemID="1968949510" typeID="208" quantity="200" flag="116" singleton="0" />
          <row itemID="1968949511" typeID="567" quantity="9" flag="117" singleton="0" />
          <row itemID="1968949535" typeID="8331" quantity="1" flag="117" singleton="0" />
          <row itemID="1968949561" typeID="230" quantity="300" flag="116" singleton="0" />
          <row itemID="1968949563" typeID="577" quantity="4" flag="117" singleton="0" />
          <row itemID="1968950651" typeID="10840" quantity="1" flag="117" singleton="0" />
          <row itemID="1968950653" typeID="575" quantity="1" flag="117" singleton="0" />
          <row itemID="1468482703" typeID="3512" quantity="1" flag="119" singleton="1" />
          <row itemID="1468482277" typeID="3512" quantity="1" flag="119" singleton="1" />
          <row itemID="1468482232" typeID="3512" quantity="1" flag="119" singleton="1" />
          <row itemID="1468482165" typeID="3512" quantity="1" flag="119" singleton="1" />
          <row itemID="2000175407" typeID="1130" quantity="1" flag="121" singleton="0" />
          <row itemID="2000175484" typeID="1132" quantity="1" flag="121" singleton="1" />
          <row itemID="2000175680" typeID="1137" quantity="1" flag="121" singleton="1" />
          <row itemID="2000307249" typeID="941" quantity="1" flag="121" singleton="1" />
        </rowset>
      </row>
      <row itemID="2002785850" locationID="30003878" typeID="3467" quantity="1" flag="0" singleton="1" />
      <row itemID="678032421" locationID="66012023" typeID="27" quantity="1" flag="4" singleton="1">
        <rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton">
          <row itemID="868921494" typeID="38" quantity="18" flag="118" singleton="0" />
          <row itemID="1897298980" typeID="892" quantity="1" flag="121" singleton="0" />
          <row itemID="1897299362" typeID="889" quantity="1" flag="121" singleton="0" />
          <row itemID="1897299386" typeID="891" quantity="1" flag="121" singleton="0" />
          <row itemID="1896789193" typeID="902" quantity="1" flag="121" singleton="1" />
          <row itemID="1896787985" typeID="899" quantity="1" flag="121" singleton="0" />
          <row itemID="1896787673" typeID="897" quantity="1" flag="121" singleton="0" />
          <row itemID="1896787248" typeID="901" quantity="1" flag="121" singleton="1" />
          <row itemID="1821279478" typeID="23712" quantity="1" flag="121" singleton="1" />
          <row itemID="1307522201" typeID="5493" quantity="1" flag="121" singleton="0" />
          <row itemID="1182384778" typeID="9848" quantity="504" flag="121" singleton="0" />
          <row itemID="833931686" typeID="1945" quantity="1" flag="121" singleton="1" />
        </rowset>
      </row>
      <row itemID="1994172764" locationID="30004976" typeID="20064" quantity="1" flag="0" singleton="1" />
      <row itemID="1994924326" locationID="30004973" typeID="16216" quantity="1" flag="0" singleton="1" />
      <row itemID="2004463926" locationID="30001647" typeID="3467" quantity="1" flag="0" singleton="1" />
    </rowset>
  </result>
  <cachedUntil>2009-04-05 12:11:23</cachedUntil>
</eveapi>

And using the import wizard in access it currently only sees two tables which is the version, current time. and cacheduntil. but no data in them or any of the main guts or data.

in excel it gets closer and has all the data but puts the certain <rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton"> bits of information against each record. So I can do it all by hand and move it in but really need something to import it.

Table example would hopefully look like:
Code: 
    <rowset name="assets" key="autoitemID" (autonumber) columns="itemID,locationID,typeID,quantity,flag,singleton, citemID,ctypeID,cquantity,cflag,csingleton"> (the c simple have there to avoid the duplicate names)

Excel puts them both into a single table which is fine, not normalized but I can manipulate it later though code. Its simple getting it in to begin with which im struggling with.

Any information or pointers would be appreciated.
 
made a little discovery...

using:

Code: 
Set xmlnodelist = xmldoc.getElementsByTagName("*")
For Each xmlnode In xmlnodelist
    For Each mynode In xmlnode.childNodes
    If mynode.nodeType = NODE_TEXT Then
        MsgBox xmlnode.nodename & "=" & xmlnode.Text
    End If
    Next mynode
Next xmlnode

it shows only the:
<currentTime>2009-04-04 13:11:23</currentTime>
and
<cachedUntil>2009-04-05 12:11:23</cachedUntil>

and not
+ <result> and its guts.
 
A new update... figure ill keep posting reports of my mission.

according to more trail and error and reading up, also the below link.

looks like my xml files are attribute-centric XML and i need to convert it to Element-centric XML first.

http://support.microsoft.com/kb/285329

Will post back when i get closer, its getting late here and im loosing steam. :(
 
i think you'll find the multiple elements inside the tags need to be handled specially

i forget the exact syntax, but i found that mynode.text or mynode.xml didnt display them all - and there was another collection that had to be examined.

can't remember what it was exactly
 
You must log in or register to reply here.

Similar threads

Rx_
One column has comma delimited data, split into a distinct row
Replies
1
Views
7,619
MOSmitty
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom