Need Help in importing the xml and xsd into access

I can`t import the xml with the xsd into access, i got an error.
plz have a look at the file, and try to help me out how to fix the problem.
thnx in regard.
Who is Participating?
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.

Jeffrey CoachmanMIS LiasonCommented:
What is the source for the XML
How are you doing the conversion to XML
drtopservAuthor Commented:
I have third party person sent the xml+xsd, it means this xml+xsd is how he need me to send  him the data,
i need to build a form in access to let users fills in the data then export them to xml and send back to the sender.
drtopservAuthor Commented:
Now, what i did is put the xml+xsd (by the way he sent another file called .xsx extention which I don`t know what is it), in a folder then tried to import the xml into access i got error, which is unspecified error (can`t processing ..xml..~ )
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

drtopservAuthor Commented:
Also, I don`t know the system of the third party and how he export this xml+xsd to me.
what i know only that i need to fill data in the same stucture of the xml he send , and give me back
Jeffrey CoachmanMIS LiasonCommented:
Not sure, but here is a sample table from access exported to XML./XSD
It import fine back into Access without issue.

Ask your 3rd part person to compare the internal structure of the two files to the structure in his two files.
Then see if they can adjust their xml output system to more closely follow the format of my sample files...
drtopservAuthor Commented:
my problem is that I have no contact with the 3 party, it hard to ask changing there format.
Is it possible to fix the issue in the files mentioned in my Q?
Jeffrey CoachmanMIS LiasonCommented:
There is no "One click" answer in Access, sorry...

You would have to write code to parse the XML file and format it the way that Access prefers...
perhaps there are tools to do this, but I am only an Access Expert, not really an expert in XML...

So lets see if an expert better at XML will chime in...

drtopservAuthor Commented:
Oh, I understand, I just thought that access 2010 have the ability to import every xml been written around, (from what i know XML have a perminant rules (W3C XML Schema) , That`s why i thought the xml+xsd i mentioned here having wrong lines that need to be fixed before imported to access.
Jeffrey CoachmanMIS LiasonCommented:
Yes, the issue was in reading the .xsd file
There were no indications in Access stating what the problem might be.
In looking at the two files, I do see some differences in the layout... This is what would probably need to be modified...
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Remove the reference to the .xsd file:

<?xml version="1.0" encoding="UTF-8" ?>
<UnusualEvent xmlns:xsi="" REMOVED>

I did that, and Access imported it fine, and created 3 tables. Obviously, you don't need the word "REMOVED" in your XML - I put it there so you'd know what I took out.

If you need more control over the import process, the only way to handle that would be through the MSXML library. Here's a nice startup tutorial on that:
drtopservAuthor Commented:
thnx LSMConsulting, i did it and it works, but what about the related xsd i can`t import it , what should i change in the xsd to let it imported?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure what you mean.

The .xsd file is really nothing more than the "schema" of an XML file. It defines the elements, element types, relationships, etc etc. You can't really import an .xsd file, nor would you want to do so.
Jeffrey CoachmanMIS LiasonCommented:
Thanks for helping out here Scott...
I was hoping someone a bit stronger in XML would chime in...
drtopservAuthor Commented:
well, still have problem in understand the xml point.
I have attached the schema screenshot in from opening the file in excel .
If we look , we will see:
tableName: Branch , then there fields (phonenumber,branchcode,depositor etc..)
now the table branch is under "report" in the tree.
the problem if , if i make a tables in access , how can i export them to xml file with same structure (tree in the image attached)
drtopservAuthor Commented:
btw, seems to run or check the xsd i should attached another file come with this xsd.
here is it
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can try to create those Tables in Access, and then create a query that meets your needs, and shows your data in the fashion you wish, and then Export that query. Note you may need to also create other "sub-queries", which you would use to build your final query, or you'd have to create temporary tables to form your data as you wish.

But understand that as you get into the more complex XML structures you'll find these methods to be lacking.

As stated earlier, if you need more control over the import (or export) of XML data, you have to manage this yourself. You'd have to create your XML Document, populate it with elements, and fill those elements with data.

There are some advanced properties of the XML Export functions -

That's for 2003, but it's relevant for newer versions as well.

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
drtopservAuthor Commented:
LSMConsulting, Bro, I have created table with relationship between them,
I could be able to export the table with with the childs table successfully by using:
right click table ->export ->xml file->then OK-> then go to "more option" , and in the data to export i just check of the table (main table and another 5 child tables.
it works .
the big problem i`m facing and i would be very glad you help me out in it is when i save this export and run in on click button in form :
DoCmd.RunSavedImportExport "exportsavedName"
it works and export the tableS to xml.
but it gives me only main table with one child under it, and then the other sub childs just jump under the main table .
how can i solve this problem, or maybe modify the hidden exportsavedName~?
drtopservAuthor Commented:
I have also seek the net i found this:
Private Sub ExportRelTables()
   ' Purpose: Exports the Orders table as well as
   ' a number of related databases to an XML file.
   ' XSD and XSL files are also created.

   Dim objAD As AdditionalData

   ' Create the AdditionalData object.
   Set objAD = Application.CreateAdditionalData

   ' Add the related tables to the object.
   With objAD
      .Add "Order Details"
      objAD(Item:="Order Details").Add "Order Details Details"
      .Add "Customers"
      .Add "Shippers"
      .Add "Employees"
      .Add "Products"
      objAD(Item:="Products").Add "Product Details"
      objAD(Item:="Products")(Item:="Product Details").Add _
         "Product Details Details"
      .Add "Suppliers"
      .Add "Categories"
   End With

   ' Export the Orders table along with the addtional data.
   Application.ExportXml acExportTable, "Orders", _
       "C:\Orders.xml", "C:\OrdersSchema.xsd", _
       "C:\OrdersStyle.xsl", AdditionalData:= objAD
End Sub

may this help me out, and how to modify it ~?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I've never used those methods to move XML data out, so can't really comment.

With that said, did you try the method with your own tables? For example, it seems you just do this:

obj.Add "TableName"

If you need to add "sub tables", you do this:

objAD(Item:="Order Details").Add "Order Details Details"

In the line above, you're adding a table named "Order Details Details" as a "child" table of the "Order Details" table.

Try that, and report back with what you find.
drtopservAuthor Commented:
thnx bro for reply,
I`ll be glad if you may have a look at this Q : ID28228077
It`s related to same problem, and there i have reach good result using another way . Maybe there you will have good idea. :}
drtopservAuthor Commented:
LSMConsulting, I got error this the code upbove :

compile error:
Named argument not found

and have a highlight in "Item:="
 objAD(Item:="Order Details").Add "Order Details Details"
drtopservAuthor Commented:
well I be able to fix the error by using:
 objAD.Item("Order Details").Add "Order Details Details"

but this doesn`t work for me :
objAD.Item("tablename")item("childTable").Add "sub-childTable"
which I need. I need the sub-childtable to be as a sub if childtable and the childtable to be the sub of the tablename
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.