export xml - slow Access 2007

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I have a temp table in Access 2007 that is the data source for export
to XML. Outputting the XML file is very slow - even when the file is
created locally (accdb is on my C:drive -- and output is to C:)

The temp table contains about 700 records of with some 40 fields.
Schema file is not created. Just a single file of element-centered
XML. Takes about 35-40 seconds to generate the file locally. This is a
relatively small data set compared others that I will need to export.
They can be as large as 14,000 records. And in production, the file
must be generated to a network location, which would presumably be
even slower.

Here's the VBA used for output:

Application.ExportXML ObjectType:=acExportQuery, DataSource:="NewGL",
_
DataTarget:="C:\TApps\Pvr\NewGL.xml"

Does anyone know of ways to make the export faster? Thanks for any
suggestions.
 
Gary said:
I have a temp table in Access 2007 that is the data source for export
to XML. Outputting the XML file is very slow - even when the file is
created locally (accdb is on my C:drive -- and output is to C:)

The temp table contains about 700 records of with some 40 fields.
Schema file is not created. Just a single file of element-centered
XML. Takes about 35-40 seconds to generate the file locally. This is a
relatively small data set compared others that I will need to export.
They can be as large as 14,000 records. And in production, the file
must be generated to a network location, which would presumably be
even slower.

Here's the VBA used for output:

Application.ExportXML ObjectType:=acExportQuery, DataSource:="NewGL",
_
DataTarget:="C:\TApps\Pvr\NewGL.xml"

Does anyone know of ways to make the export faster? Thanks for any
suggestions.
Click to expand...

Once you know the structure of the XML file, why not hard code strings
for the parts that don't change? Then you can base a recordset on the
query and fill arrays with the values in a single pass using ADO or DAO.
After that, printing the variable XML part to the output file should
proceed quite rapidly within, say, two loops. Note that the array index
can only go up to 32,767 if it is not mathematically shifted to utilize
the negative indices, so 14K records should fit. It would be
interesting to compare the time you get with the time that ExportXML takes.

James A. Fortune
(e-mail address removed)
 
Very fast indeed. Utilizing an array, the XML file is created within
about 3 seconds!

However, when I try to open the newly created file, I get the
following error . . .

************************************
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and
then click the Refresh button, or try again later.

Invalid at the top level of the document. Error processing resource
'file:///C:/TApps/Pvr/NewGL.xml'. Line 1, Position 40
<?xml version="1.0" encoding="UTF-8" ?>
***********************************************

I've read a few posts regarding this error. Some suggest it is caused
by whitespace above the XML, but I don't know how to determine if it
actually exists - or how to get rid of it if it does.

So that I can see the output, I've printed it as text file. Here's a
sample . . .

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
generated="2009-09-01T13:49:53">
- <NewGL>
<YEAR> 2009 </YEAR> <TCODE>
1354 </TCODE> <SPAN>
999-999-99999</SPAN> <SCHID>
109 </SCHID> <PARCID>
41100219.700 </PARCID> <CNTY>
1 </CNTY> <ASMTCODE>
354 </ASMTCODE> <OWNER1>
145 CORE LLC </OWNER1> <GLVAL_HS>
0 </GLVAL_HS> <GLVAL_NR>
1513 </GLVAL_NR> </NewGL>
- <NewGL>
</dataroot>


And here's what that looks like when generated with ExportXML
method . . .

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
generated="2009-09-01T14:20:44">
- <NewGL>
<YEAR>2009</YEAR>
<TCODE>1354</TCODE>
<SPAN>999-999-99999</SPAN>
<SCHID>109</SCHIDL>
<PARCID>41100219.700</PARCID>
<CNTY>1</CNTY>
<ASMTCODE>354</ASMTCODE>
<OWNER1>145 CORE LLC</OWNER1>
<GLVAL_HS>0</GLVAL_HS>
<GLVAL_NR>1513</GLVAL_NR>
</NewGL>
</dataroot>


I'm writing out the static parts of the file in this way . . .

strHeader1 = "<?xml version=""1.0"" encoding=""UTF-8"" ?>"
strHeader2 = "- <dataroot xmlns:od=""urn:schemas-microsoft-
com:officedata"" generated=""2009-09-01T13:49:53"">"
Print #1, strHeader1
Print #1, strHeader2

Can anyone see why I get the error - and if so, what might be the
resolution?
 
Gary said:
Very fast indeed. Utilizing an array, the XML file is created within
about 3 seconds!

However, when I try to open the newly created file, I get the
following error . . .

************************************
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and
then click the Refresh button, or try again later.

Invalid at the top level of the document. Error processing resource
'file:///C:/TApps/Pvr/NewGL.xml'. Line 1, Position 40
<?xml version="1.0" encoding="UTF-8" ?>
***********************************************

I've read a few posts regarding this error. Some suggest it is caused
by whitespace above the XML, but I don't know how to determine if it
actually exists - or how to get rid of it if it does.

So that I can see the output, I've printed it as text file. Here's a
sample . . .

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
generated="2009-09-01T13:49:53">
- <NewGL>
<YEAR> 2009 </YEAR> <TCODE>
1354 </TCODE> <SPAN>
999-999-99999</SPAN> <SCHID>
109 </SCHID> <PARCID>
41100219.700 </PARCID> <CNTY>
1 </CNTY> <ASMTCODE>
354 </ASMTCODE> <OWNER1>
145 CORE LLC </OWNER1> <GLVAL_HS>
0 </GLVAL_HS> <GLVAL_NR>
1513 </GLVAL_NR> </NewGL>
- <NewGL>
</dataroot>


And here's what that looks like when generated with ExportXML
method . . .

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
generated="2009-09-01T14:20:44">
- <NewGL>
<YEAR>2009</YEAR>
<TCODE>1354</TCODE>
<SPAN>999-999-99999</SPAN>
<SCHID>109</SCHIDL>
<PARCID>41100219.700</PARCID>
<CNTY>1</CNTY>
<ASMTCODE>354</ASMTCODE>
<OWNER1>145 CORE LLC</OWNER1>
<GLVAL_HS>0</GLVAL_HS>
<GLVAL_NR>1513</GLVAL_NR>
</NewGL>
</dataroot>


I'm writing out the static parts of the file in this way . . .

strHeader1 = "<?xml version=""1.0"" encoding=""UTF-8"" ?>"
strHeader2 = "- <dataroot xmlns:od=""urn:schemas-microsoft-
com:officedata"" generated=""2009-09-01T13:49:53"">"
Print #1, strHeader1
Print #1, strHeader2

Can anyone see why I get the error - and if so, what might be the
resolution?
Click to expand...

I'd say the next step would be to use the Trim() function and set up the
loop so that the data appears exactly the same way that ExportXML
generates it. Once that is ruled out, maybe convert the strings to
Unicode using strConv() before doing a Binary write to the file. If
that fails, use a Hex Editor to see where the files differ because if
one can be viewed and the other can't, then they have to differ somewhere.

James A. Fortune
(e-mail address removed)
 
I'd say the next step would be to use the Trim() function and set up the
loop so that the data appears exactly the same way that ExportXML
generates it.  Once that is ruled out, maybe convert the strings to
Unicode using strConv() before doing a Binary write to the file.  If
that fails, use a Hex Editor to see where the files differ because if
one can be viewed and the other can't, then they have to differ somewhere..

James A. Fortune
(e-mail address removed)- Hide quoted text -

- Show quoted text -
Click to expand...


It's working now - and I'm pleased with the result -- 5 seconds to
output instead of 45. Being new to XML, I had included the "-" and "+"
handles in the static code that I wrote out. The "Invalid at the top
level of the document" error went away after that change. Also had a
few other adjustments to make to that my output matched ExportXML
output exactly.

Thanks for your help! This technique will be very useful.
 
Gary said:
It's working now - and I'm pleased with the result -- 5 seconds to
output instead of 45. Being new to XML, I had included the "-" and "+"
handles in the static code that I wrote out. The "Invalid at the top
level of the document" error went away after that change. Also had a
few other adjustments to make to that my output matched ExportXML
output exactly.

Thanks for your help! This technique will be very useful.
Click to expand...

Thanks for the information. Seeing some hard numbers gives me a better
feel for how much time parts of the ExportXML process take.

James A. Fortune
(e-mail address removed)
 
Back
Top