Getting the XML Data

---

There are many different ways in which you can get XML data on the server, including:

• Using ADO 2.1 or later to get XML information directly from any database that supports ADO.
• Reading data from a database or other source and constructing an XML tree using the DOM.
• Loading an XML file using the XML control's load method.
• Constructing a string and loading it into the XML control using the loadXML method.
• Constructing an XML text string directly, without building an XML document object on the server.

Several of these methods are illustrated in the following sections.

Constructing an XML Tree Using the DOM

The XML Document Object Model (DOM) provides the ability to programmatically construct an XML document from data read from a database or other data source.

The following code reads data from a database and constructs an XML document using the DOM. The document is then sent to the client as an XML text stream.

<% Response.ContentType = "text/xml" %>
<?xml version="1.0" encoding="windows-1252" ?>
<%

' Check to see if a document has data. If it does, don't build it

if (xmldoc.childNodes.length = 0) then

  ' Build the XML document

  set root = xmldoc.createNode("element","SalesData","")
  xmldoc.appendChild(root)

  ' Queries the database for customer data

  SQL = "SELECT OrderID, CompanyName, OrderDate, ShippedDate, " & _
        "       ShipCity, ShipState " & _
        " FROM Customers " & _
        "INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID "
  set Conn = Server.CreateObject("ADODB.Connection")
  Conn.Open("pdc")
  set rs = Conn.Execute(SQL)

  ocount = 0

  rs.MoveFirst()
  while (not rs.EOF and ocount < 10)
    set onode = xmldoc.createNode("element","Order","")
    xmldoc.documentElement.appendChild(onode)

    for i = 0 to rs.Fields.Count - 1
      set child = xmldoc.createNode("element",rs.Fields(i).Name,"")
      child.text = rs.Fields(i)
      onode.appendChild(child)
    next

    pcount = 0

    set isnode = xmldoc.createNode("element","Items","")
    onode.appendChild(isnode)

    iSQL = "select ProductName, OrderDetails.UnitPrice as UnitPrice," & _
           "       Quantity from OrderDetails" & _
           " inner join Products on Products.ProductID = OrderDetails.ProductID" & _
           " where OrderID = "
    iSQL = iSQL & rs("OrderID")

    set irs = Conn.Execute(iSQL)

    irs.MoveFirst()

    while (not irs.EOF and pcount < 3)
      set inode = xmldoc.createNode("element","Item","")
      isnode.appendChild(inode)

      for i = 0 to irs.Fields.Count - 1
        set child = xmldoc.createNode("element",irs.Fields(i).Name,"")
        child.text = irs.Fields(i)
        inode.appendChild(child)
      next

      irs.MoveNext()
      pcount = pcount + 1
    wend

    rs.MoveNext()
    ocount = ocount + 1
  wend

end if

Response.Write(xmldoc.xml)

%>

The DOM methods createNode and appendChild and the text property are used to construct the XML tree. The xml property writes the document out as an XML stream, which is then sent to the client.

Loading XML from a File

The DOM load method can be used to build a DOM document from a URL.

xObj = Server.CreateObject("Microsoft.XMLDOM");
xObj.load(Server.MapPath("simple.xml"));

The MapPath method resolves the relative path to a full path.

Directly Constructing XML Text

If you do not need to process or cache the XML on the server, you can choose to simply construct the XML text without using the XML control. This does not require the XML control to be installed on the server.

The following example reads data from a database and injects it into the output stream.

<% Response.ContentType = "text/xml" %>
<?xml version="1.0" encoding="windows-1252" ?>

<SalesData>
<%

SQL = "SELECT OrderID, CompanyName, OrderDate, ShippedDate, " & _
      "       ShipCity, ShipState " & _
      " FROM Customers " & _
      "INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID " & _
      "ORDER BY ShipState"
set Conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")

Conn.Open("pdc")
set rs = Conn.Execute(SQL)

rs.MoveFirst()
while (not rs.EOF)
%>
  <Order>
    <OrderID> <%= rs("OrderID") %> </OrderID>
    <Customer> <%= rs("CompanyName") %> </Customer>
    <OrderDate> <%= rs("OrderDate") %> </OrderDate>
    <ShippedDate> <%= rs("ShippedDate") %> </ShippedDate>
    <ShipCity> <%= rs("ShipCity") %> </ShipCity>
    <ShipState> <%= rs("ShipState") %> </ShipState>
  </Order>

<%    
    rs.MoveNext()
wend

rs.close()
conn.close()

%>
</SalesData>

Since the XML tags are generated directly as text, the overhead of building the DOM document object is avoided.