MS Access XML File Importing Techniques and Ideas For Linking Separate Tables

I am going to try and explain this the best I can and I hope everyone understands and can help.  
*****I am not sure if in the XML file what I am referring to is called a Node, Boundary Tag, or Element. So I will call it a Node.****

I am importing a XML file into MS Access by VBA and when I do this Access separates the Children Nodes from the Parent Node and makes the Children Nodes tables and then puts the Grandchildren Nodes in the Children table as Field Names.


Here is a XML sample:
<?xml version="1.0" encoding="utf-8"?>
<SampleXML>
	<Transaction>
		<Orders>
			<OrderID>1234</OrderID>
		</Orders>
		<Customer>
			<CustomerFNm>John</CustomerFNm>
			<CustomerLNm>Doe</CustomerLNm>
		</Customer>
	</Transaction>
	<Transaction>
		<Orders>
			<OrderID>6789</OrderID>
		</Orders>
		<Customer>
			<CustomerFNm>Jim</CustomerFNm>
			<CustomerLNm>Johnson</CustomerLNm>
		</Customer>
	</Transaction>
</SampleXML>

Open in new window


In my scenario here Access Makes these table:
Transaction
Orders
Customers

These Fields:
Orders.OrderID
Customer.CustomerFNm
Customer.CustomerLNm


So how can I get Access to either import all the Children and Grandchildren into a single table or how can I link these tables to show John Doe was OrderID 1234????


Thank you for the help?
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you using the ImportXML feature? If so, it's pretty limited, and if you want extended functionality you'd have to parse and manage the XML import yourself using the DOM library. It's not too terribly difficult, but it can take a bit to get your head around.

You may, however, be able to use a Transform file to "map" your XML to the way you want. Essentially the Transform file is an xlst file that you use with the ImportXML feature. You create this file yourself, and then include it in your call to ImportXML.

Here's a link to solution that uses the transform file: https://stackoverflow.com/questions/30029303/how-to-import-xml-with-nested-nodes-parent-child-relationships-into-access
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks Scott. I was hoping there was an easier way than mapping it. I have looked into this for a previous task but it is for sure something that takes some time to wrap your head around. Hopefully someone else might chime in and have some other helpful info. That link definitely is a good source of info though. Thanks.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks Scott. I have not had time to deploy this but this honestly seems like a very plausible answer for my situation. I will just have to map out the new XML sheet and after that it should go well.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I just came back to say THANK YOU SCOTT!!!! I finally had some time to set down and learn the Transforming XML and that link REALLY Paid off. I now know enough to be dangerous :).....Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.