I was looking for a quick way to convert
a query to an XML file and found this KB
article:
How to bind a Data Access Page to XML in Access 2002
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q281863
Anyway, it did work really slick, but I stumbled
at first on (wrong in both sections)
3.On the Insert menu, click Reports.
that should be
3.On the Insert menu, click Pages.
This really did work great for what I was
trying to do....
We receive txt files from our university that are
essentially a print out of the line schedule for a term.
They are not delimited so the parsing into our db
can be complicated and slow.
They are retiring the mainframe and I wanted to
test what an xml file might look like if it were possible
in the new system, i.e., all the fields that are important
to us, can I then import them into db, etc.
I know there are probably many ways to do this...
But, I made a query of one term, then followed kb:
2. In the Database window, click the table or query.
****the name of your query will be in essence
the "root" of your xml file***
3. On the Insert menu, click Pages.
4. Click AutoPage: Columnar, and then click OK.
After the page opens, click Design View on the View menu
to open the page in Design view .
5. Right-click the page, and then click Page Properties.
Click the Data tab.
6. Change the UseXMLData property to True.
This instructs the DataSourceControl on the page
to bind to an XML source.
7. Change the XMLLocation property to dscXMLDataFile.
8. Change the XMLDataTarget property to a local or network path.
The file name should use an .XML extension.
This cannot be an HTTP path.
***This will be your "saved" xml file***
9. On the Tools menu, point to Macro, and then click Visual Basic Editor.
10. In the Immediate window, type the following line, and then press
ENTER:
DataAccessPages(0).MSODSC.ExportXML
It turned out that fields that contained "mixed data" I ended up
wrapping in
Trim([field] & "")
in the query and the only fields in the next xml that ended
up using "CDATA" were ones that had the special
"entity" characters
<, &, >, ", '
I tried a simple import from the File menu and
only problem was it chopped off a few fields that
originally were memo fields and were >255 chars.
I guess both problems (mixed and >255) can be
overcome with a schema? but the important thing
was that I quickly had a well-formed xml that I
could demonstrate, and knew import would then
be a breeze if can convince powers heading this
changeover.
Again, I know there are many other ways,
but it worked simply for me in this situation.
gary
