XML Import

[gemma-the-husky]

Can someone give me a general guide to this

i have a file of invoices

each invoice has a header, and multiple lines

if you import using external file/import access just constructs tables of each individual xml tag, populated with the various values, not linked in any way

how do you get an invoice table, and invoice line table, etc, populated with appropriate data.

 

[Uncle Gizmo]

I don't know the answer, but I thought I could make a suggestion.

I would be inclined to create a table in the way I would like the data to appear populated with Example data, export that to XML and then have a look at the structure and see if it gave me any ideas.

 

[gemma-the-husky]

ive been searching around - ive downloaded a demo app that uses an reference to a MS XML 3.0 dll library.

it appears to load in the xml elements to a collection type object within vba, and then must presumably traverse the tree of objects in some way to retrieve data

so you have your tables, and then populste them by examining the tree object

now i just need to find some help for the library ...

 

[John.Woody]

Search for xml under user john.woody. I have posted an example of what you are looking for.

John

 

[gemma-the-husky]

john

i found a thread of yours with some code from Kernel K, enabling direct reading of an attribute. Is that what you meant?

i also found and downloaded the official help documentation for the xml dll which is
xmlsdk30.chm

 

[John.Woody]

Look further down the post theres a sample xml file starts <mycomputer> and the code is there to read through the xml file.

 

[gemma-the-husky]

John

thanks ive got it now

starting at the top of the thread didnt find it for some reason

messing around with what i had, i already developed a recursive scan of all the nodes, and also extracted the data to a typed variable and then saved it into a table

i have a problem in identifying which subnodes similarly named attributes are dervied from eg many lines called addressline, all referring to different addresses

i see your parsing mech is similarly recursive, although you have a couple of extra parameters. I will look at your code with interest

thanks

[edited - added this section in subseqently]

cracked the subnode issue now, having seen how you used parentnode property
i used

.parentnode..parentnode.parentnode.basevalue
to establish the subtree type

NOTE: In your code you pass the whole doc to each iteration of the parse module. Surely you dont need to do that, as that is the whole document anyway.

 

[grovelli]

Hi, I have a problem very similar to yours.
I have downloaded invoices in XML form from a web trader portal.
I attach an example XML file and the associated dtd file (XMLInvoices.zip).
I can import the xml file into Access but the following, very
important, bit of info, gets left out:
<odn num="508001475889"> which is the invoice number. Is there a way to
get that into Access as well?

 

[gemma-the-husky]

i dont know about the odnnum (theres a trader cdisp as well). i m not sure technically how to describe it, but the odnum forms part of the tag, rather than a leaf node. Is this an attribute?

i didnt need to dereference the tags, i just needed to get the leaf nodes, but im sure its possible to examine the tags.

thinking about it, if you are specifically looking for "odn num", then when you navigate by a node, you can examine the basevalue for odn num, or maybe more generally look for an = sign

the trouble is help on this seems so hard to come by, so its a bit of trial and error.

i'll have a look

------
btw are you processing the xml by tree-walking, or do you have an easier way?

 

[grovelli]

I'm not an expert in XML by all means.
"when you navigate by a node, you can examine the basevalue for odn num, or maybe more generally look for an = sign"
Do you that using VBA?
And what's a leaf node inside the XML file? The only place I've ever heard about leaf nodes is here.
Thanks for your patience, hopefully you'll shed more light for me about this.

 

[gemma-the-husky]

an xml file is like an upside down tree

at the top is a root

from that is a number of branches (nodes)

each node can have more child nodes. if there are no more nodes, then you have reached a leaf node, which will contain something of importance, and can be interrogated.

i think in some cases the nodes themselves contain information other than just the name of the node (hence the references to examining the node itself).

-------

structures of this type lends themselves to recursive examination, which looks very elegant, as you accomplish a lot without many lines of code. In fact trying to traverse a tree without recusrion is really awkward. (this pseudocode is a bit simplified, but this does it)

sub examinenode(thisnode as nodetype)

if thisnode.haschildren then
   for each node.childnode
      {may need to process the node as well, either before or after examining child nodes}
       examinenode(childnode) {the main bit - this calls this procedure again starting from the child node this time, ie climbing down the tree}
   next
else
    {now we cant go down anyfurther, so we climb back up the tree}
   processleafnode
end if
end sub

sub main
examinenode(root)
end sub

the problem is that tree structures are stored in ram, and need pointer data types (similar in some ways to collections in vb) which are not available in vb - therefore you need to obtain a module written in C or somrthing similar, (eg MS XML library), and find out how to handle the properties/methods, which is the hard bit.


I learned about trees and other data structures (queues/hashing algorithms etc) using a book called Data Structures by Stubbs and Webre (using pascal). A long while ago (1985 book), but the theory is probably still valid.

 

[gemma-the-husky]

i've got it now

the attributes (in your case odn num = 123456 etc)
are stored in an extra data structure attached to each node

therefore to get at these you have to examine this structure.

-------
more to come

 

[gemma-the-husky]

when you say you can import the stuff into access, do you get anything meaningful

i thought the dtd file might help, but it doesnt seem to - it just seems to load all the data in each field type, but not in a relational way. did you achieve something differnet?

 

[grovelli]

I've found the attached mdb but don't know whether it could be of any help in properly importing those xml invoice files.