Importing XML into Access

[mdstg]

I am trying to import an XML file into Access 2002. The XML file has sub tags. Here is a sample below:

<?xml version=&quot;1.0&quot;?>
<SalesExport StartDate=&quot;04/01/2002&quot; EndDate=&quot;04/23/2002&quot;>
<Invoice>
<StoreNum>4</StoreNum>
<InvoiceNum>598</InvoiceNum>
<InvoiceDate>04-22-2002</InvoiceDate>
<InvoiceType>Normal</InvoiceType>
<InvoiceTotal>10.70</InvoiceTotal>
<InvoiceItem>
<Quantity>8.00</Quantity>
<ALU>DC00000003</ALU>
<Cost>0.00</Cost>
<Price>1.25</Price>
<ExtTotal>10.00</ExtTotal>
</InvoiceItem>
</Invoice>

The problem is that access will import the XML doc into two tables without maintaining any relationship. Ideally I would like the InvoiceItem table to contain the <InvoiceNum> field from the root of the XML doc. I think I need to define an XSD but I am not sure where to start. Can anyone point in the right direction maybe a tutorial link?

Thanks

 

[Drainie]

I'm also having the same problem with import into Access. Something to do with <InvoiceItem> and </InvoiceItem> being on separate lines.

Since you posted your query, have you made any progress?