Question on importing xml to Access

[Guest]

Hi there,

We want to import XML data from a web service into a local Access database.
There is a XML schema applied to the XML data before importing. We expected
that the Access database schema should be defined by the XML schema but the
result is not like what we thought. What defines the database schema when
importing XML file into Access (the XML file structure or the XML schema
applied to this file)?

Thanks.

Yolande

 

[Guest]

The following is more details for our problem.

We have the XML file and the XML schema (ws_ticket.xsd) applied to it. After
we import the XML file, the database contains more tables than we defined in
the XML schema. It also includes the tables created based on the XML file
structure. For example, we have the following XML file

<?xml version="1.0" encoding="UTF-8" ?>
<thqwebservice xsi:noNamespaceSchemaLocation="ws_ticket.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ticket ticketid="92e41518-ec67-47d9-8976-b29aa533c010">
<objname>041201112521482</objname>
<comment>testing</comment>
<title>A remote test</title>
<product productid="8b0112a6">
<product>THQ 4.9.1</product>
</product>
<customer customerid="11d62206">
<customer>IBM</customer>
<departmentname>Technology Division</departmentname>
<fname>Yolande</fname>
<lname>Feng</lname>
</customer>
</ticket>
</thqwebservice>

And the XML schema (ws_ticket.xsd)is
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="thqwebservice">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ticket" type="ticketType" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>

<xsd:complexType name="customerType">
<xsd:sequence>
<xsd:element name="customer" type="xsd:string" />
<xsd:element name="departmentname" type="xsd:string" />
<xsd:element name="fname" type="xsd:string" />
<xsd:element name="lname" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="customerid" type="xsd:string" />
</xsd:complexType>

<xsd:complexType name="productCodeType">
<xsd:sequence>
<xsd:element name="product" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="productid" type="xsd:string" />
</xsd:complexType>

<xsd:complexType name="ticketType">
<xsd:sequence>
<xsd:element name="objname" type="xsd:string" />
<xsd:element name="comment" type="xsd:string" />
<xsd:element name="title" type="xsd:string" />
<xsd:element name="product" type="productCodeType" />
<xsd:element name="customer" type="customerType" />
</xsd:sequence>
<xsd:attribute name="ticketid" type="xsd:string" use="required" />
</xsd:complexType>

After imported the XML file, the result database schema contains the
following tables:
thqwebservice, ticketType, customerType, productCodeType (these table do not
have data)
customer, ticket, product (these table have data from the XML file)

The result looks like the Access creates tables based on both the XML schema
(but no data for these tables) and the XML structure (contains data). What we
want is that the Access database schema is based on only the XML schema
(ws_ticket.xsd) and the database contains data from XML file.

The XML schema (ws_ticket.xsd) worked properly when we used it to import XML
data into EXCEL.

What shall we do for this problem? Thanks again.

Yolande