XML to Access

G

Guest

First off, I know nothing about XML files.

I'm trying to import an XML file into Access 2003. It works fine, but it
creates 2 seperate tables; Order and Order_Details. How do i match the data?
It should be a one to many relationship. I'm totally confused, any help would
be appreciated.

Rodger
 
J

Jeff Boyce

Rodger

We're not there, so we can't see the data.

If I were setting up an Order/OrderDetail database, I'd use the primary key
from Order as a foreign key in OrderDetail. Do you have a "common"
fieldname between the two tables ... and is it something like OrderID?
 
G

Guest

Here is the data, I didn't see any keys to match.

<?xml version="1.0" encoding="ISO-8859-1"?>
<order_file>
<order>
<project>NOVO</project>
<site_code>BR</site_code>
<order_number>2001</order_number>
<po_number>br615482</po_number>
<billto_number>01000</billto_number>
<billto_name>Homer Simpson</billto_name>
<billto_company>SNP</billto_company>
<billto_address1>710 Evergreen Terrace</billto_address1>
<billto_address2></billto_address2>
<billto_city>Springfield</billto_city>
<billto_prov_state>OH</billto_prov_state>
<billto_postal_code>10150</billto_postal_code>
<billto_country>US</billto_country>
<billto_phone>555-555-5555</billto_phone>
<billto_email></billto_email>
<shipto_number>01000</shipto_number>
<shipto_name>Homer Simpson</shipto_name>
<shipto_company>SNP</shipto_company>
<shipto_address1>710 Evergreen Terrace</shipto_address1>
<shipto_address2></shipto_address2>
<shipto_city>Springfield</shipto_city>
<shipto_prov_state>OH</shipto_prov_state>
<shipto_postal_code>10150</shipto_postal_code>
<shipto_country>US</shipto_country>
<shipto_phone>555-555-5555</shipto_phone>
<shipto_email>[email protected]</shipto_email>
<entry_date>10/7/2005</entry_date>
<order_date>10/7/2005</order_date>
<requested_date>10/17/2005</requested_date>
<ship_via></ship_via>
<ship_method></ship_method>
<partial_order></partial_order>
<instructions></instructions>
<order_details>
<order_detail>
<order_linenum>1</order_linenum>
<item>S8465969</item>
<qty_ordered>10</qty_ordered>
</order_detail>
<order_detail>
<order_linenum>2</order_linenum>
<item>Y5843659</item>
<qty_ordered>50</qty_ordered>
</order_detail>
</order_details>
</order>
<order>
<project>NOVO</project>
<site_code>BR</site_code>
<order_number>2002</order_number>
<po_number>br846596</po_number>
<billto_number>02000</billto_number>
<billto_name>Waylan Smithers</billto_name>
<billto_company></billto_company>
<billto_address1>123 Maple Dr.</billto_address1>
<billto_address2></billto_address2>
<billto_city>Vancouver</billto_city>
<billto_prov_state>BC</billto_prov_state>
<billto_postal_code>L7H 5R5</billto_postal_code>
<billto_country>Canada</billto_country>
<billto_phone>555-555-5555</billto_phone>
<billto_email></billto_email>
<shipto_number>02000</shipto_number>
<shipto_name>Waylan Smithers</shipto_name>
<shipto_company></shipto_company>
<shipto_address1>123 Maple Dr.</shipto_address1>
<shipto_address2></shipto_address2>
<shipto_city>Vancouver</shipto_city>
<shipto_prov_state>BC</shipto_prov_state>
<shipto_postal_code>L7H 5R5</shipto_postal_code>
<shipto_country>Canada</shipto_country>
<shipto_phone>555-555-5555</shipto_phone>
<shipto_email></shipto_email>
<entry_date>10/7/2005</entry_date>
<order_date>10/5/2005</order_date>
<requested_date>10/31/2005</requested_date>
<ship_via></ship_via>
<ship_method></ship_method>
<partial_order></partial_order>
<instructions></instructions>
<order_details>
<order_detail>
<order_linenum>1</order_linenum>
<item>G3754846</item>
<qty_ordered>15</qty_ordered>
</order_detail>
<order_detail>
<order_linenum>2</order_linenum>
<item>T4895837</item>
<qty_ordered>10</qty_ordered>
</order_detail>
<order_detail>
<order_linenum>3</order_linenum>
<item>A8465945</item>
<qty_ordered>100</qty_ordered>
</order_detail>
</order_details>
</order>
</order_file>
 
J

Jeff Boyce

Rodger

I'm guessing, then, that any orderlinenumber "underneath" an OrderNumber
therefore "belongs" to that Order.

Jeff Boyce
<Office/Access MVP>
 
J

Joe Fallon

Rodger,
You can't relate the details to the orders using the existing XML data.

You need to modify the XML data to include the order_number on each detail
record too.
Then afte you import the data to Access you will get 2 tables (order and
order_detail) which can be related by order_number using a query like this:

SELECT order.project, order.site_code, order.order_number, order.po_number,
order_detail.order_linenum, order_detail.item, order_detail.qty_ordered
FROM [order] INNER JOIN order_detail ON order.order_number =
order_detail.order_number
ORDER BY order.order_number, order_detail.order_linenum;
 
G

Guest

Strange thing is, when I open it in Excel, it comes out the way I want it to.



Joe Fallon said:
Rodger,
You can't relate the details to the orders using the existing XML data.

You need to modify the XML data to include the order_number on each detail
record too.
Then afte you import the data to Access you will get 2 tables (order and
order_detail) which can be related by order_number using a query like this:

SELECT order.project, order.site_code, order.order_number, order.po_number,
order_detail.order_linenum, order_detail.item, order_detail.qty_ordered
FROM [order] INNER JOIN order_detail ON order.order_number =
order_detail.order_number
ORDER BY order.order_number, order_detail.order_linenum;
--
Joe Fallon




Rodger said:
Here is the data, I didn't see any keys to match.

<?xml version="1.0" encoding="ISO-8859-1"?>
<order_file>
<order>
<project>NOVO</project>
<site_code>BR</site_code>
<order_number>2001</order_number>
<po_number>br615482</po_number>
<billto_number>01000</billto_number>
<billto_name>Homer Simpson</billto_name>
<billto_company>SNP</billto_company>
<billto_address1>710 Evergreen Terrace</billto_address1>
<billto_address2></billto_address2>
<billto_city>Springfield</billto_city>
<billto_prov_state>OH</billto_prov_state>
<billto_postal_code>10150</billto_postal_code>
<billto_country>US</billto_country>
<billto_phone>555-555-5555</billto_phone>
<billto_email></billto_email>
<shipto_number>01000</shipto_number>
<shipto_name>Homer Simpson</shipto_name>
<shipto_company>SNP</shipto_company>
<shipto_address1>710 Evergreen Terrace</shipto_address1>
<shipto_address2></shipto_address2>
<shipto_city>Springfield</shipto_city>
<shipto_prov_state>OH</shipto_prov_state>
<shipto_postal_code>10150</shipto_postal_code>
<shipto_country>US</shipto_country>
<shipto_phone>555-555-5555</shipto_phone>
<shipto_email>[email protected]</shipto_email>
<entry_date>10/7/2005</entry_date>
<order_date>10/7/2005</order_date>
<requested_date>10/17/2005</requested_date>
<ship_via></ship_via>
<ship_method></ship_method>
<partial_order></partial_order>
<instructions></instructions>
<order_details>
<order_detail>
<order_linenum>1</order_linenum>
<item>S8465969</item>
<qty_ordered>10</qty_ordered>
</order_detail>
<order_detail>
<order_linenum>2</order_linenum>
<item>Y5843659</item>
<qty_ordered>50</qty_ordered>
</order_detail>
</order_details>
</order>
<order>
<project>NOVO</project>
<site_code>BR</site_code>
<order_number>2002</order_number>
<po_number>br846596</po_number>
<billto_number>02000</billto_number>
<billto_name>Waylan Smithers</billto_name>
<billto_company></billto_company>
<billto_address1>123 Maple Dr.</billto_address1>
<billto_address2></billto_address2>
<billto_city>Vancouver</billto_city>
<billto_prov_state>BC</billto_prov_state>
<billto_postal_code>L7H 5R5</billto_postal_code>
<billto_country>Canada</billto_country>
<billto_phone>555-555-5555</billto_phone>
<billto_email></billto_email>
<shipto_number>02000</shipto_number>
<shipto_name>Waylan Smithers</shipto_name>
<shipto_company></shipto_company>
<shipto_address1>123 Maple Dr.</shipto_address1>
<shipto_address2></shipto_address2>
<shipto_city>Vancouver</shipto_city>
<shipto_prov_state>BC</shipto_prov_state>
<shipto_postal_code>L7H 5R5</shipto_postal_code>
<shipto_country>Canada</shipto_country>
<shipto_phone>555-555-5555</shipto_phone>
<shipto_email></shipto_email>
<entry_date>10/7/2005</entry_date>
<order_date>10/5/2005</order_date>
<requested_date>10/31/2005</requested_date>
<ship_via></ship_via>
<ship_method></ship_method>
<partial_order></partial_order>
<instructions></instructions>
<order_details>
<order_detail>
<order_linenum>1</order_linenum>
<item>G3754846</item>
<qty_ordered>15</qty_ordered>
</order_detail>
<order_detail>
<order_linenum>2</order_linenum>
<item>T4895837</item>
<qty_ordered>10</qty_ordered>
</order_detail>
<order_detail>
<order_linenum>3</order_linenum>
<item>A8465945</item>
<qty_ordered>100</qty_ordered>
</order_detail>
</order_details>
</order>
</order_file>
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

XML into Access 2003 3
Importing XML Data in Access 2003 1
Import xml to Access 2007 question 0
Problems Importing Govt. XML Files 1
Importing strucrtured XML data int Access 2007 1
Export to XML - error 31532 0
Access-->XML-->Excel: Size Limitations? 0
Access 2007 fails to execute saved XML import 0

Top