Export Access query as XML and read into dotnet DataSet??

[M O J O]

Hi,

(I'm new to XML)

I can export data from Access as an XML file (with schema integrated in
the XML file). I can read it into a DotNet DataSet, but the schema is
not correct that is - I do not get the right columns.

Is there some kinda trick to convert the XML to a
DotNet-DataSet-readbable file??

Thanks!

M O J O

[Dino Chiesa [Microsoft]]

Why don't you just query Access directly from .NET?
Why go through the step of exporting to XML, then trying to read it in?

what are you really trying to accomplish?

"M O J O" <mojo@_no_spam_delete_this_newwebsolutions.dk> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Hi,

(I'm new to XML)

I can export data from Access as an XML file (with schema integrated in
the XML file). I can read it into a DotNet DataSet, but the schema is
not correct that is - I do not get the right columns.

Is there some kinda trick to convert the XML to a
DotNet-DataSet-readbable file??

Thanks!

M O J O

[Cowboy \(Gregory A. Beamer\)]

You best best is to use ADO.NET to connect to Access rather than use its XML
file. In the future, this may be different, but the Access format is not the
same as the DataSet format of XML.

If you have to move from Access to DataSet, I would look at creating a XSLT
stylesheet and transforming to the DataSet XML using it. This is a much more
reuseable method of conversion. Not sure if anyone has already tackled this
monster, so I would do a search before writing it myself.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"M O J O" <mojo@_no_spam_delete_this_newwebsolutions.dk> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Hi,

(I'm new to XML)

I can export data from Access as an XML file (with schema integrated in
the XML file). I can read it into a DotNet DataSet, but the schema is
not correct that is - I do not get the right columns.

Is there some kinda trick to convert the XML to a
DotNet-DataSet-readbable file??

Thanks!

M O J O

[Cor]

Hi Mojo,

In addition to the other messages, when you want to use your access direct,
you can of course connect direct.

But when you want to use it as an export file I think that if you cannot
read an XML as a dataset you can always convert it using the XMLreader or
the LoadXML (the document object model).

A dataset is not that difficult to make although as it sometimes look.

Dim ds As New DataSet
Dim dt As New DataTable("Active")
dt.Columns.Add(New DataColumn("String", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Ident", Type.GetType("System.Int32")))
Dim keys(0) As DataColumn
keys(0) = dt.Columns("Ident")
dt.PrimaryKey = keys
ds.Tables.Add(dt)

I hope this helps?

Cor

[M O J O]

Hi Dino,

I'm creating a program that will accept data from several sources
(Access, Excel and so on).

So I want to use common interface to accept these sources.

Therefor I need to be able to easily read the XML file from access.

M O J O

Dino Chiesa [Microsoft] wrote:

Why don't you just query Access directly from .NET?
Why go through the step of exporting to XML, then trying to read it in?

what are you really trying to accomplish?

"M O J O" <mojo@_no_spam_delete_this_newwebsolutions.dk> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Hi,

(I'm new to XML)

I can export data from Access as an XML file (with schema integrated in
the XML file). I can read it into a DotNet DataSet, but the schema is
not correct that is - I do not get the right columns.

Is there some kinda trick to convert the XML to a
DotNet-DataSet-readbable file??

Thanks!

M O J O

[M O J O]

Please see my answer to Dino.

XSLT? I can hardly spell it! :o)

I just wanted it to be easy.

M O J O

Cowboy (Gregory A. Beamer) wrote:

You best best is to use ADO.NET to connect to Access rather than use its XML
file. In the future, this may be different, but the Access format is not the
same as the DataSet format of XML.

If you have to move from Access to DataSet, I would look at creating a XSLT
stylesheet and transforming to the DataSet XML using it. This is a much more
reuseable method of conversion. Not sure if anyone has already tackled this
monster, so I would do a search before writing it myself.

[Kevin Yu [MSFT]]

Thanks for Gregory, Dino and Cor's response.

Hi Mojo,

I agree with Gregory's idea to write an XSLT to transform the Xml to the
DataSet Xml. This is the simplest way if you want to use XML as the data
source, or you can connect to the Access file directly.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Kevin Yu [MSFT]]

Hi Mojo,

You can load xml directly into DataSet...
If the XSD schema is provided the DataSet would contain the schema.
If not it infers the schema from the Xml file...
and the columnNames will be the same as the element/attribute names in the
Xml file.

IF a Access Query is exported to Xml file, the structure is :
<dataroot>
<Products>
<ProductID>1</ProductID>
<ProductName>name1</ProductName>
</Products>
<Products>
</Products>
</dataroot>

Therefore DataSet will interpret as two tables, dataroot and Products.
But still you will be getting the elementNames as the columnNames.

C# Code:
========
DataSet ds = new DataSet();
ds.ReadXmlSchema(@"c:\products.xsd");
ds.ReadXml(@"C:\Products.xml");

Note: Access 2000 creates the XSD schema with old Schema Namespace, you
will have to change it to:
xmlns:xsd="http://www.w3.org/2001/XMLSchema"

When you load the schema with old namespace, DataSet throws an Exception.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Dino Chiesa [Microsoft]]

Access and Excel can both be queried via ADO.NET - using connection strings,
if I am not mistaken. this will get you a dataset, without having to first
"export to XML".

What's the "and so on"?

ADO.NET can do CSV, SQL server, other databases... ?

"M O J O" <mojo@_no_spam_delete_this_newwebsolutions.dk> wrote in message
news:OI**************@TK2MSFTNGP11.phx.gbl...

Hi Dino,

I'm creating a program that will accept data from several sources
(Access, Excel and so on).

So I want to use common interface to accept these sources.

Therefor I need to be able to easily read the XML file from access.

M O J O

Dino Chiesa [Microsoft] wrote:

Why don't you just query Access directly from .NET?
Why go through the step of exporting to XML, then trying to read it in?

what are you really trying to accomplish?

"M O J O" <mojo@_no_spam_delete_this_newwebsolutions.dk> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl...

Hi,

(I'm new to XML)

I can export data from Access as an XML file (with schema integrated in
the XML file). I can read it into a DotNet DataSet, but the schema is
not correct that is - I do not get the right columns.

Is there some kinda trick to convert the XML to a
DotNet-DataSet-readbable file??

Thanks!

M O J O

[Paul Clement]

On Thu, 11 Dec 2003 20:05:06 +0100, M O J O <mojo@_no_spam_delete_this_newwebsolutions.dk> wrote:

¤ Hi Dino,
¤
¤ I'm creating a program that will accept data from several sources
¤ (Access, Excel and so on).
¤
¤ So I want to use common interface to accept these sources.
¤
¤ Therefor I need to be able to easily read the XML file from access.
¤
¤ M O J O
¤
¤ Dino Chiesa [Microsoft] wrote:
¤ > Why don't you just query Access directly from .NET?
¤ > Why go through the step of exporting to XML, then trying to read it in?
¤ >
¤ > what are you really trying to accomplish?
¤ >
¤ > "M O J O" <mojo@_no_spam_delete_this_newwebsolutions.dk> wrote in message
¤ > news:%2****************@TK2MSFTNGP10.phx.gbl...
¤ >
¤ >>Hi,
¤ >>
¤ >>(I'm new to XML)
¤ >>
¤ >>I can export data from Access as an XML file (with schema integrated in
¤ >>the XML file). I can read it into a DotNet DataSet, but the schema is
¤ >>not correct that is - I do not get the right columns.
¤ >>
¤ >>Is there some kinda trick to convert the XML to a
¤ >>DotNet-DataSet-readbable file??
¤ >>

I don't see the need for ADO here. Access, Excel, dBase, etc can all be queried directly from
ADO.NET (with the ODBC and OLEDB namespaces) using the same ODBC and OLEDB connections that are used
under ADO.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)