Xml

[gemma-the-husky]

What am I missing?

what is the purpose of import xml?

if I have an xml file of invoices structured like this (and in practice its much more complex, but this gives you the idea)

batch
invoice
header
line
ticketnumber
date
qty
line
ticketnumber
date
qty
invoice
header
line
ticketnumber
date
qty
line
ticketnumber
date
qty
trailer

-------------
if i just import this table by importing the xml, each of these attributes end up in a table of there own - with no relation between them.

instead what i want is

a) a table of invoice numbers
b) a table of invoicelines LINKED to the invoice numbers
etc

-----------------
is this what style sheets, and transforms are for? - because otherwise it just seems a waste of time!


----------------
what I have ended up doing is recursively walking all the nodes of the xml tree, and manually extracting the information I need - which is very quick, but is a bit of a brute force approach (akin to importing a csv file by manually reading in and parsing each row) but I am sure I am missing a simpler solution.


and the other thing thats the devils own job is extracting attributes from a node! (or even realising that they are there to extract - becuase if you open an xml in notepad, you can see them - but with an xml viewer you cant (without knowing you are looking for something)

 

[Banana]

I'm not sure how you are importing this but I suspect that Access has no idea how to import the XML because it doesn't know what is the XSLT that's used to describe the structure. This was of some interest to me.

What the article tells me, though, is that there is no simple way of parsing the XSLT and building an appropriate structure in Access. IOW, it's manual. So, either you'd re-write the utility in the article or use Visual Studio, which has better XML handling capabilities and write a library for Access to consume.

 

[gemma-the-husky]

thanks for the link, but it didnt really help a lot, i dont think. looks like everyone has similar problems


what i have done is use the msxml library

i examine the file structure manually so i know exactly what i am looking for

than i walk the tree, starting from the root, and storing important node values in data structures, discarding some, and then write them to proper RDBS tables with sql insert statements

i use a basic recursive tree walker algorithm to walk the tree

 

[Galaxiom]

Your structure looks to be Attribute-Centric XML.
Access imports work with Element-Centric XML.

Element-Centric would look something in the style of:

<batch>
     <invoice>
         <header>value</header>
         <line>
              <ticketnumber>1</ticketnumber>
              <date>01/01/10</date>
              <qty>6</qty>
          </line>
          <line>
              <ticketnumber>2</ticketnumber>
              <date>01/01/10</date>
              <qty>8</qty>
          </line>
          <trailer>whatever</trailer>
     </invoice>
</batch>

This might help but I haven't read it.
http://support.microsoft.com/kb/285329

 

[wazz]

i wonder about xml sometimes too. it does always seem to be a brute force approach and it's a bit annoying. i don't think there's any way around it. don't know about import xml. can you read 'node types'? in c# you have to do something like this:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]while[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] (reader.Read())[/SIZE]
[SIZE=2]{[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]  switch[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] (reader.NodeType)[/SIZE]
[SIZE=2]  {[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] XmlNodeType.Element:[/SIZE]
[SIZE=2]        MyString += [/SIZE][SIZE=2]reader.Name;[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]        if[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] (reader.IsEmptyElement)[/SIZE]
[SIZE=2]           etc.;[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     break[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] XmlNodeType.Comment:[/SIZE]
[SIZE=2]        MyString += [/SIZE][SIZE=2]reader.Value[/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     break[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] XmlNodeType.Text:[/SIZE]
[SIZE=2]        MyString += [/SIZE][SIZE=2]reader.Value[/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     break[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] XmlNodeType.XmlDeclaration:[/SIZE]
[SIZE=2]        MyString += [/SIZE][SIZE=2]reader.Name + [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] + reader.Value[/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     break[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] XmlNodeType.EndElement:[/SIZE]
[SIZE=2]        MyString += [/SIZE][SIZE=2]reader.Name[/SIZE][SIZE=2];[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]     break[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2];[/SIZE]
[SIZE=2]  } [/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]// End of: switch[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]} [/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]// End of: while[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]return[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] MyString;[/SIZE]

 

[stopher]

My knowledge of the practical use of XML is limited to some coursework I did last year so I'm probably stabbing in the dark here.

Is it possible/reasonable to use the XML DOM? I'm guessing that's what Wazz's C# code is implementing?

Some code from the object model.
http://msdn.microsoft.com/en-us/library/d271ytdx.aspx

Chris

 

[stopher]

Here's some condensed code based on code and the exmaple XML file from here. Obviously the print statements can be replaced with suitable code to write to respective tables.

Dim oDoc As MSXML2.DOMDocument
Dim fSuccess As Boolean
Dim oRoot As MSXML2.IXMLDOMNode
Dim oCountry As MSXML2.IXMLDOMNode
Dim oAttributes As MSXML2.IXMLDOMNamedNodeMap
Dim oCountryName As MSXML2.IXMLDOMNode
Dim oChildren As MSXML2.IXMLDOMNodeList
Dim oChild As MSXML2.IXMLDOMNode

Set oDoc = New MSXML2.DOMDocument

' Load the  XML from disk, without validating it. Wait
' for the load to finish before proceeding.
oDoc.async = False
oDoc.validateOnParse = False
fSuccess = oDoc.Load("c:\temp\traffic.xml")
 ' If anything went wrong, quit now.
If Not fSuccess Then
  'code for handling failure goes here
End If

' Get the root of the XML tree.
Set oRoot = oDoc.documentElement

' Go through all children of the root.
For Each oCountry In oRoot.childNodes
    ' Collect the attributes for this country/region.
    Set oAttributes = oCountry.Attributes
    ' Extract the country/region name and
    ' place it on the worksheet.
    Set oCountryName = oAttributes.getNamedItem("CountryName")
    Debug.Print oCountryName.Text
  
    ' Go through all the children of the country/region node.
    Set oChildren = oCountry.childNodes
    
    For Each oChild In oChildren
        ' Get information from each child node to the sheet.
        If oChild.nodeName = "TotalVisits" Then
            Debug.Print "tot vis", oChild.nodeTypedValue
        End If
        If oChild.nodeName = "LatestVisit" Then
            Debug.Print "lat vis", oChild.nodeTypedValue
        End If
    Next oChild

Next oCountry

 

[gemma-the-husky]

wazz/stopher/galaxiom

a) the invoice tree I exampled is structured like the element centric model you note. My description was just so you could see the layout. It does have outer tags, and nested inner tags etc.

b)
I DO use XML DOm (ie msxml library)

Initially I load the xml file, to test "errorresult", which validates the xml file is well formed

c)
I posted this some time ago - it walks an xml file recursivley, using the XML DOM model, and generates an access table showing the hierarchical nodes it encounters.

since it does it recursively its easy to identify the node sequence that led to this node


it needs you to create the tables with the correct fields - and some people have reported minor problems - but it definitely works.

http://www.access-programmers.co.uk/forums/showthread.php?t=166861

 

[jdraw]

wazz/stopher/galaxiom

a) the invoice tree I exampled is structured like the element centric model you note. My description was just so you could see the layout. It does have outer tags, and nested inner tags etc.

b)
I DO use XML DOm (ie msxml library)

Initially I load the xml file, to test "errorresult", which validates the xml file is well formed

c)
I posted this some time ago - it walks an xml file recursivley, using the XML DOM model, and generates an access table showing the hierarchical nodes it encounters.

since it does it recursively its easy to identify the node sequence that led to this node


it needs you to create the tables with the correct fields - and some people have reported minor problems - but it definitely works.

http://www.access-programmers.co.uk/forums/showthread.php?t=166861

Dave,
I tried the code at the link provided. I created a table for tblXMLNodes and modified some code to get around an error with a missing field in the table.
It loaded a "not so pretty XML" file that was discussed in another forum.

I have a couple of basic questions after doing this parse to table stuff.

Have you thought about using your tblXMLNodes table and processing it to build tables and fields?
Would such be practical, if for nothing more than helping most of us who don't really know XML, but need to get XML data into Access tables?

Based on the parsing, I still don't see how you would load data. Anybody have a process for that?

 

[vbaInet]

From my understanding, XML on its own is meaningless data. You need XSLT to give it definition. XML was made more for web objects and interoperability between web objects. For importing into Access it could be useful for importing XML definitions for customised ribbons in Access 2007, which is saved into the USysRibbons table or coded via COM add-ins.

 

[jdraw]

From my understanding, XML on its own is meaningless data. You need XSLT to give it definition. XML was made more for web objects and interoperability between web objects. For importing into Access it could be useful for importing XML definitions for customised ribbons in Access 2007, which is saved into the USysRibbons table or coded via COM add-ins.

But that's what started this post. Access 2003 and 2007 have an IMPort XML feature. None of us can make much sense out of this. So, Dave (GTH), wrote some code to parse nodes and store in a tblxmlnodes table.
So, if one can get the proper tables from parsing. Could we automate the whole shootin' match-- parse, create the tables and load the data?

 

[vbaInet]

Possibly. I think you need to have the signature at the root for it to work. The signature includes the encoding and version of access. Something like:

<?xmlns encoding="" version="1.0"?> or

<?xml encoding="" version="1.0"?>

Don't quote me on that though. Not sure about the encoding value.

 

[Banana]

If the import XML was supposed to be universal, I would have had expected it to ask for XSLT file, because as vbaInet, XML structure is meaningless without an accompanying XSLT file to tell what structure this is. I can write a XML file using my structure and vbaInet can do the same but the structure could be different, hence the need for XSLT.

Last time I toyed with this, (via an ADO recordset, but I've not tried via Access UI) I decided that the XML support was um, weak.

 

[gemma-the-husky]

Dave,
I tried the code at the link provided. I created a table for tblXMLNodes and modified some code to get around an error with a missing field in the table.
It loaded a "not so pretty XML" file that was discussed in another forum.

I have a couple of basic questions after doing this parse to table stuff.

Have you thought about using your tblXMLNodes table and processing it to build tables and fields?
Would such be practical, if for nothing more than helping most of us who don't really know XML, but need to get XML data into Access tables?

Based on the parsing, I still don't see how you would load data. Anybody have a process for that?

i just built this routine, so i can easily see the structure of the xml file.

i use a similar tree walker to extract the data, and write it into proper the relational structure that i want to end up with.

eg with an invoice
i get the invoice header node - now i can traverse all the child nodes of this, and i know that each node is either a direct attribute of the header (eg date/invoice number/ etc) or maybe its an invoiceline entity - in which case, i can travers the subnodes of this to pick up all the bits of the invoicelines - and i know thet are all related to the current invoice. the nodes are really easy to handle because you can use parent.parent.parent syntax etc, to any depth

once you understand the recursive algorithm its not too hard at all.

-----------
i will also go the other way in due course - i am sure ts quite easy to produce an xml file as its just a text file, you just need some code to write the "open tag", generate any further tags, and then write the "close tag" - if you see what i mean - but i will do this by processing some record sets.

i have never got round to trying to use xslt's and stuff like that - so i dont know if there is an easier way of changing rdbs data into an xml file

 

[gemma-the-husky]

If the import XML was supposed to be universal, I would have had expected it to ask for XSLT file, because as vbaInet, XML structure is meaningless without an accompanying XSLT file to tell what structure this is. I can write a XML file using my structure and vbaInet can do the same but the structure could be different, hence the need for XSLT.

Last time I toyed with this, (via an ADO recordset, but I've not tried via Access UI) I decided that the XML support was um, weak.

the thing is in practice the edi files are all slightly different even if they supposedly adhere to a standard. eg I am actually trying to analyse invoice files sent adhering to the Tradex standard

now all the xml files are well-formed - but they are all slightly different. in one case address are held as subtags within a "party tag", with tags of

(ignoring the closing tags)
party
--addressline1
--addressline2
--addressline3
--addressline4
--postcode

in another - otherwise the same its instead
party
--addressline
--addressline
--addressline
--addressline
--addressline

(and its especially awkward in this latter example - as the tags are all the same)

there are other differences as well


so in practice you cant have a standard routine and expect it to work without testing and checking. - you have to take account of all these slight differences

---------
I couldnt find anything within access help or online etc, to deal with directly importing xml - but the xmldom (msxml library) made it possible. You could try to analyse the tree by parsing the text file in code - but to do this properly I think you really need a pointer data type - so its hard/hardly possible in VBA

 

[vbaInet]

Dave have a look at this link:

http://office.microsoft.com/en-us/access/HA010345601033.aspx

 

[gemma-the-husky]

Possibly. I think you need to have the signature at the root for it to work. The signature includes the encoding and version of access. Something like:

<?xmlns encoding="" version="1.0"?> or

<?xml encoding="" version="1.0"?>

Don't quote me on that though. Not sure about the encoding value.

I ignored those bits as i couldnt work out how to usefully use the style sheets


html/xml etc etc - at its basic level its just a text file <open tag> DATA <close tag>


so you could just read it in and parse it as a text file if you wanted - but thats all been done for you in the DOM model - and it really would be pointless trying to replicate that!

 

[vbaInet]

I see. Did you see the link I posted? It's got a pretty easier XSLT definition. What do you think?

XSLT is like CSS is to XHTML.

 

[Banana]

vbaInet -

Looks like there is a mechanism for XSLT & transforming XML now. Good to know. Apparently it's also necessary to edit the original XML's header to reference to the XSLT.

 

[vbaInet]

Banana:

It's good isn't it. It's all in preparation for Access 2010 I think. Access is looking very web capable.