Xml Data Islands by Bob Barrows

Right-click and choose View Source to see the data in the data island.

Click here to generate a table bound to the data in the island:

Code:<!--#include virtual="/testsite/global_include.asp" -->
<%
dim sXML, cn, rs, sSQL,arData,arNames,xmldoc

'Insert your database server details:
set cn=server.CreateObject("ADODB.Connection")
cn.Open Application("ConnectionString")

sSQL =  "Select CompanyName,ContactName, City FROM Northwind..Suppliers"

set rs = cn.Execute(sSQL,,adCmdText)

if not rs.EOF then
    CreateRSarrays rs,arData,arNames
    rs.Close
    set rs = nothing
    cn.Close
    set cn = nothing
    set xmldoc = BuildXMLfromRSarrays(arData,arNames)
    sXML= xmldoc.xml
    erase arData
    erase arNames
else
    rs.Close
    set rs = nothing
    cn.Close
    set cn = nothing
    sXML=  "<row companyname=""No data returned by query""/>"
end if

function BuildXMLfromRSarrays(parData, parNames)
    dim i,j, oNode, oRoot,xmldoc, sRootName, sNodeName
    sRootName =  "rows"
    sNodeName =  "row"
    set xmldoc = Server.CreateObject("msxml2.domdocument")
    set oRoot = xmldoc.createElement(sRootName)
    set xmldoc.documentElement = oRoot
    for i = 0 to ubound(parData,2)
        set oNode=xmldoc.createElement(sNodeName)
        oNode.setattribute  "nodeid",i
        for j = 0 to ubound(parNames,1)
            oNode.setattribute parNames(j,0),parData(j,i)
        next   'j
        oRoot.appendchild oNode
    next   'i
    'Response.Write xmlData.xml
    Set BuildXMLfromRSarrays = xmldoc
    set xmldoc=nothing
    set oRoot = nothing
    set oNode = nothing
end function

sub CreateRSarrays(pRS,parData,parNames)
    Dim i
    parData = pRS.GetRows
    redim parNames(pRS.Fields.Count-1,1)
    for i = 0 to pRS.Fields.Count-1
        parNames(i,0) = lcase(pRS.Fields(i).Name)
        parNames(i,1) = lcase(pRS.Fields(i).Type)
    next  'i
end sub

%>
<HTML>
<HEAD>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <title>Xml Data Islands by Bob Barrows</title>
    <link rel="stylesheet" href="/Templates/style.css" type="text/css" />
<SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>
<!--

Sub button1_onclick
xmlsuppliers.insertadjacenthtml  "beforeBegin", _
 "<table datasrc=""#xmlSuppliers"" border=1><tr>" & _
 "<td><span dataFld=""companyname""></span></td>" & _
 "<td><span dataFld=""contactname""></span></td>" & _
 "<td><span dataFld=""city""></span></td></tr></table>"
End Sub

-->
</SCRIPT>
</HEAD>
<BODY>
<h3>Xml Data Islands by Bob Barrows</h3>
Right-click and choose View Source to see the data in
the data island.<BR>
           <BR>
Click here to generate a table bound to the data in the island:
<INPUT type="button" id=button1 name=button1
style="WIDTH: 15px; HEIGHT: 17px">

<XML id=xmlSuppliers>
<%=sXML%>
</XML>

<br /><br /><pre>
<%
displayAspFile Server.MapPath("xml.data.islands.asp")
%>
</pre>
</BODY>
</HTML>