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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.