convert query into xml-file

  • Thread starter Thread starter Ceno
  • Start date Start date
C

Ceno

Hello,

In order to communicate with a server that only understands xml-files I want to
convert an Access2000-query into a xml-file.
Can somebody tell if that is possible and how can I accomplish such a task?

Ceno
 
You can use the Save method of an ADO recordset to save the contents of the
recordset as XML ...

Public Sub SaveRecordset()

Dim rst As New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Open "SELECT CategoryID, CategoryName, Description FROM Categories"
.ActiveConnection = Nothing

'After running this procedure once, you'll need to uncomment the
following commented
'lines, or manually delete the Categories.xml file, if you want to
run it again.
'On Error Resume Next
'Kill CurrentProject.Path & "\Categories.xml"
'On Error GoTo 0
.Save CurrentProject.Path & "\Categories.xml", adPersistXML
.Close
End With

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thanks for your suggestion.
Unfortunely I (still) have a DAO recordset and maybe because of that the
parameters are not recognized.
I presume that the name "Categories" is that of the recordset and the ID and
Name extensions are the parameters.
Is there an easy way to transform from DAO to ADO?
I even couldn't discard the DAO-reference in a new instance of a MS Access
database.

Ceno
 
Ceno said:
Thanks for your suggestion.
Unfortunely I (still) have a DAO recordset
Click to expand...

You can use both DAO and ADO in the same Access application. You just have
to be careful to 'disambiguate' when objects with the same name exist in
both object libraries, e.g. "Dim rst AS DAO.Recordset" or "Dim rst As
ADODB.Recordset" rather than just "Dim rst As Recordset". DAO doesn't have a
..Save method, you'll need to use ADO for that.
and maybe because of that the
parameters are not recognized.
Click to expand...

I'm not sure what parameters you mean. But the code I posted is necessarily
ADO-specific, because DAO does not have a save-as-XML feature. The code will
certainly not work with a DAO recordset in place of the ADO recordset.
I presume that the name "Categories" is that of the recordset
Click to expand...

No, Categories is the name of the table. This is an example using the
Categories table from the Northwind sample database that comes with Access.
and the ID and
Name extensions are the parameters.
Click to expand...

CategoryID, CategoryName and Description are all names of fields in the
Categories table.
Is there an easy way to transform from DAO to ADO?
Click to expand...

I don't think so, but I'm not entirely sure what you mean by 'transform' in
this context.
I even couldn't discard the DAO-reference in a new instance of a MS Access
database.
Click to expand...

I don't know why you would be unable to remove the reference. But it is not
necessary to remove it in order to use ADO.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
I got it working and the code generated a xml file of a query. Great !
The lay-out of that xml file however differs so much from the one I need.

This are the first lines of the xml file file layout that are wanted
<?xml version="1.0" encoding="UTF-16" ?>
- <LEDEN>
<RECORDCOUNT>0</RECORDCOUNT>
<FIELDCOUNT>23</FIELDCOUNT>
</LEDEN>

And these are the first lines I have generated

- <xml xmlns:s="uuid:xx" xmlns:dt="uuid:xxx"
xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
- <s:Schema id="RowsetSchema">
- <s:ElementType name="row" content="eltOnly">
- <s:AttributeType name="LIDNR" rs:number="1" rs:nullable="true"
rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
rs:fixedlength="true" />
</s:AttributeType>
(I changed the uuid's)

Is the 'xml generating tool' embedded in Windows 2000 or in Access2000 (I use
DAO3.6 / ADO 2.5)
Can you shed your light upon this matter too?

Ceno
 
Sorry, I can't help much with that. I expect you'll need to use an XSL
Transform (XSLT) to transform the XML from the ADO recordset schema to the
schema required by your target application, but I am no expert on XSLT.

There's an introduction to XSLT, with links to related articles and
background information, at the following URL ...

http://msdn.microsoft.com/msdnmag/issues/0800/xslt/TOC.asp

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top