Creating XML from an Access Database

[blondebier]

I've created the following file to generate XML from my access database. I'm testing it locally using IIS and when I run the ASP file it seems to slow my computer vastly.
There is a lot of HDD activity but nothing seems to happen. There aren't any error messages either.

<%@LANGUAGE=&quot;VBScript&quot;%>
<!-- #include file=&quot;datastore.asp&quot; -->
<% Response.ContentType = &quot;text/xml&quot; %>

<% strDate = Year(Now) & &quot;-&quot; & Right(&quot;00&quot; & Month(Now), 2) & &quot;-&quot; &_
Right(&quot;00&quot; & Day(Now), 2) %>
<?xml version=&quot;1.0&quot; ?>
<dailyReports =&quot;<% = strDate %>&quot;>
<intro>This is an XML Document of the Database</intro>
<% '-- select all the report details --
QUOT = Chr(34)
On Error Resume Next
Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot
oConn.Open strConnect
strSQL=&quot;SELECT * FROM Data ORDER BY File&quot;
Set oRs = oConn.Execute(strSQL)
Do While Not oRs.EOF
strContact = oRs(&quot;Contact&quot
strAddress = oRs(&quot;Address&quot
strTown = oRs(&quot;Town&quot
strCounty = oRs(&quot;County&quot
strPostcode = oRs(&quot;Postcode&quot
strTelephone = oRs(&quot;Telephone&quot
%>
<report>
<Contact><% = strContact %></Contact>
<Address><% = strAddress %></Address>
<Town><% = strTown %></Town>
<County><% = strCounty %></County>
<Postcode><% = strPostcode %></Postcode>
<Telephone><% = strTelephone %></Telephone>
</report>
<%
oRs.MoveNext
Loop
oRs.Close
oConn.Close
Set oConn = Nothing
Set oRs = Nothing
%>
</dailyReports>

Is this the best way to go about it?

 

[whichman]

Normally with ADO you should do this:

Set oRs = oConn.Execute(strSQL)
oRS.Save(&quot;C:\temp\myfile.xml&quot

But from what I understand, you want it customized. So you should do it as you did it before but just watch out for &quot;<&quot; and &quot;&&quot; characters as such:

Do While Not oRs.EOF
strContact = FixXML(oRs(&quot;Contact&quot & &quot;&quot
strAddress = FixXML(oRs(&quot;Address&quot & &quot;&quot
strTown = FixXML(oRs(&quot;Town&quot& &quot;&quot
strCounty = FixXML(oRs(&quot;County&quot& &quot;&quot
strPostcode = FixXML(oRs(&quot;Postcode&quot& &quot;&quot
strTelephone = FixXML(oRs(&quot;Telephone&quot& &quot;&quot
%>
<report>
<Contact><% = strContact %></Contact>
<Address><% = strAddress %></Address>
<Town><% = strTown %></Town>
<County><% = strCounty %></County>
<Postcode><% = strPostcode %></Postcode>
<Telephone><% = strTelephone %></Telephone>
</report>
<%
oRs.MoveNext
Loop


The code for FixXML will be:

Function FixXML(strText)
FixXML = Replace(Replace(strText,&quot;&&quot;,&quot;&&quot,&quot;<&quot;,&quot;<&quot
End Function


Build web applications faster with a few lines of XML Code using DataML[tm]

http://dataml.net

 

[nidgep]

there is a buglet in Internet Explorer where you must follow the content type line with the
<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; ?>

shown below is a snippet of your code, note the placement of the xml declaration. Also not sure about the line which reads <dailyReports=&quot;<%......%>&quot;>.
this seems to suggest that the document root is
<dailyReports> as well as being an attribute. Is this correct.
I would have assumed that the document root would have been something like this:
<dailyreport date=&quot;<%=strDate%>&quot;>
...
</dailyreport>



<%@LANGUAGE=&quot;VBScript&quot;%>
<!-- #include file=&quot;datastore.asp&quot; -->
<% strDate = Year(Now) & &quot;-&quot; & Right(&quot;00&quot; & Month(Now), 2) & &quot;-&quot; &_
Right(&quot;00&quot; & Day(Now), 2) %>

<% Response.ContentType = &quot;text/xml&quot; %><?xml version=&quot;1.0&quot; ?>
<dailyReports date=&quot;<% = strDate %>&quot;>
....
</dailyReports>



Hope this is of some help.