XML with ASP

The following example illustrates how to create an XML tree (in memory) and then persist is to disk (using the save method).

Here we have created an XMLDOM Object. We then create a Root node and its child node using the createNode function. Finally we append the nodes after assigning the text property to nodes. In the end we save the in-memory XML tree to a file.

We can also build an XML file from the results of a database query. The following example illustrates how to accomplish this. (For this example I used the pubs database, which comes along with SQL Server.)

<%
 'Open database connection
 Set conn = Server.CreateObject("ADODB.Connection")
 dsn = "DSN=pubs;UID=sa;PWD="
 conn.Open dsn

 'Create XMLDOM Object
 Dim xmldoc
 Set xmldoc = Server.CreateObject("Microsoft.XMLDOM")

 If (xmldoc.childNodes.length = 0) Then
   ' Build the XML document
   Set root = xmldoc.createNode("element", "Hi-Tech", "")

   xmldoc.appendChild (root)

   ' Queries the database for customer data
   Sql = "select au_lname,au_fname,au_id from authors"
   Set rs = conn.Execute(Sql)
 
   rs.MoveFirst

   'Loop through the recordset
   Do While Not rs.EOF
     Set onode = xmldoc.createNode("element", "Employee", "")
     xmldoc.documentElement.appendChild (onode)
 
     Set inode = xmldoc.createNode("element", "Name", "")
     inode.Text = rs.fields(0) & " " & rs.fields(1)
     onode.appendChild (inode)

     'Grab another recordset based on the authorID
     Sql = "select title_id,royaltyper from titleauthor " & _
           "where au_id = '" &  rs.fields(2) & "'"

     Set rs2 = conn.Execute(Sql)

     If Not (rs2.EOF = True And rs2.bof = True) Then
       Set inode = xmldoc.createNode("element", "Titles", "")
       onode.appendChild (inode)
       Set child = xmldoc.createNode("element", "TitleId", "")
       child.Text = rs2.fields(0)
       inode.appendChild (child)
       Set child = xmldoc.createNode("element", "royalty", "")
       child.Text = rs2.fields(1)
       inode.appendChild (child)

       rs2.Close
       Set rs2 = Nothing
     End If
 
     rs.movenext
   Loop

   Set rs = Nothing
 End If

 'Save the XML doc
 xmldoc.save server.mappath("saved.xml")


 '------ DISPLAY THE XML DATA ------------
 ' Linking XML and XSL together
 sourceFile = Server.MapPath("saved.xml")
 styleFile = Server.MapPath("saved.xsl")
 
 set source = Server.CreateObject("Microsoft.XMLDOM")
 source.async = false
 source.load(sourceFile)
 set style = Server.CreateObject("Microsoft.XMLDOM")
 style.async = false
 style.load(styleFile)
 Response.Write source.transformNode(style)
%>

In the above example we first make the connection to our SQL Server database using Connection Object of ADO. Next, we create the recordset, populating it with the names of our authors in the authors table. We populate a second recordset based on the current authors ID. Eventually, all of this data is incorporated into the XML Tree using the createNode function and finally appending the nodes. Finally, the XML data is displayed using an XSL stylesheet. Remember that XML is designed to not store information on how the data should be displayed. Rather, XML is used only as a holding place for data. To turn an XML document into a nice-looking HTML document, you need to use XSL.