database output to xml

[ClaireJones]

<%LANGUAGE=&quot;VBScript&quot;%>

<%
'Open database connection
dim Cn
dim Rs

Set Cn = Server.CreateObject(&quot;ADODB.Connection&quot
Cn.Open &quot;Organizer&quot;
Set Rs = Server.CreateObject(&quot;ADODB.Recordset&quot
Set Rs = Cn.Execute(&quot;Select * From Schedule&quot

dim strXML
strXML = &quot;&quot;

'Write XML content to variable strXML
strXML = strXML & &quot;<?xml version='1.0'?>&quot;
strXML = strXML & &quot;<Agenda>&quot;

Do until rs.EOF
strXML = strXML & &quot;<Events>&quot;
strXML = strXML & &quot; <date>&quot;
strXML = strXML & &quot; <Day>&quot; & rs.fields(&quot;nDay&quot & &quot;</Day>&quot;
strXML = strXML & &quot; <Month>&quot; & rs.fields(&quot;nMonth&quot & &quot;</Month>&quot;
strXML = strXML & &quot; <Year>&quot; & rs.fields(&quot;nYear&quot & &quot;</Year>&quot;
strXML = strXML & &quot; </date>&quot;
strXML = strXML & &quot; <Event>&quot;
strXML = strXML & &quot; <Time>&quot; & rs.fields(&quot;nStartTime&quot & &quot;</Time>&quot;
strXML = strXML & &quot; <Title>&quot; & rs.fields(&quot;nEvent&quot & &quot; </Title>&quot;
strXML = strXML & &quot; <Location>&quot; & rs.fields(&quot;nLocation&quot & &quot; </Location>&quot;
strXML = strXML & &quot; <Comment>&quot; & rs.fields(&quot;nText&quot & &quot; </Comment>&quot;
strXML = strXML & &quot; </Event>&quot;
strXML = strXML & &quot;</Events>&quot;
rs.MoveNext
Loop

strXML = strXML & &quot;</Agenda>&quot;

'Close and deallocate objects
Rs.Close
Set Rs = Nothing
Cn.Close

'Load the XML into an XML object
Set data = Server.CreateObject(&quot;Microsoft.XMLDOM&quot
data.loadXML(strXML)

dataPath = Server.MapPath(&quot;Agenda.xml&quot
data.save(dataPath)

%>

 

[XMLStick]

A nice piece of code!

 

[chiph]

Claire -
Be careful when using string concatenation when building XML. For an example of what can go wrong, insert a &quot;&&quot; (ampersand) into one of your database rows (like using &quot;Smith & Sons&quot; for a company name).

You'll produce invalid XML, as that character (and a few others like the less-than & greater-than symbols) must be escaped by converting them to &amp;amp; &amp;lt; and &amp;gt; sequences.

Chip H.

 

[XMLStick]

Chiph,
Your mention about &amp; &lt; &gt; will make a sense if ClaireJones would parse his final XML output by using oldest versions of some XML parsers. Latest parsers do not care about these characters. Also, if you use an XML for some your data transformation purpose it doesn’t meter too.

 

[tembalena]

Once you've created the Agenda.xml file, and have saved your output to it, how would you then append an extra record to the end of the file's content?

 

[ClaireJones]

Well the way I'm doing it write now is everytime I add a new record to the database though an asp page I have a response.redirect to the above page which regenerates the xml file and copies over the old one. The new record is then visable.