Contents of xmls2ke2.asp:
<html> <head> <SCRIPT language="javascript"> var c=1; xml_string="<p>"; function ol() { var root=data_island.documentElement; if (data_island.parseError !=0) alert(data_island.parseError.reason); treewalk(root) xout.innerHTML = xml_string+"<p>Number of rows="+parseInt(c); } function treewalk(thisnode) { for(i=0;i<thisnode.childNodes.length;i++) { xml_string=xml_string+"<br><"+thisnode.childNodes.item(i).nodeName+"><br> <nobr>"; for(j=0;j<thisnode.childNodes(i).childNodes.length;j++) { xml_string=xml_string+"<"+thisnode.childNodes(i).childNodes(j).nodeName+">"; xml_string=xml_string+thisnode.childNodes(i).childNodes(j).text; xml_string=xml_string+"</"+thisnode.childNodes(i).childNodes(j).nodeName+">"; } xml_string=xml_string+"</nobr><br></"+thisnode.childNodes(i).nodeName+">"; c=c+1; } xout.innerHTML=xml_string; } </SCRIPT> </head> <body bgcolor="#cccccc" onLoad="ol()"> <table border="1"> <tr><td valign='middle' bgcolor='#999999'><img src="captsm.gif"></td> <td valign='middle' bgcolor='#999999'><center><font color="#ffffff"><font face="Comic Sans MS"> <br> <b>A SIMPLE XML EXTRACT USING SQL SERVER 2000<br> "SELECT...FOR XML AUTO, ELEMENTS"</b> <br> </td></tr></table> </center> <p> <font face="Comic Sans MS" size="2"> <p> The SQL connection string is ="SELECT * from glmaster <b>FOR XML AUTO, ELEMENTS</b>" <p> Note: The connection is OLEDB <u>NOT</u> DSN. <p> MS SQL Server 2000 returns 1 nested tag <u>per</u> table row where: <ol> <li>the outside tag is the table name <li>each table row's "SELECTED" columns appears as a tag pair with the column data as content <li>there is <u>NO</u> overall outside tag<br> (Note: You have to add one to use the XML DOM on the client side) </ol> <p> The XML is returned to the client as an XML data island and rolled out through javascript using the XML DOM.<br> NOTE: The roll out is not recursive since the "XML AUTO" will only return two levels. <p> Click "VIEW", then "SOURCE" to see the XML data island and the javascript XMLDOM rollout Execution begins... <p> <form name="xmltest"> <XML id="data_island"><?xml version="1.0"?><outside_tag> <% set cn=Server.CreateObject("ADODB.Connection") cn.open "Provider=SQLOLEDB; Data Source=AUCKLAND; Initial Catalog=gl001; User ID =gl001; Password=11111;" set cm=Server.CreateObject("ADODB.Command") set cs=CreateObject("ADODB.Stream") set cm.ActiveConnection = cn cm.CommandType = 1 '*** Text cm.CommandText="SELECT * from glmaster FOR XML AUTO, ELEMENTS" cs.mode=1 cs.Type=2 cs.open cm.Properties("Output Stream").value = cs cm.execute,,1024 Response.Write cs.ReadText cn.close set cn=nothing set cm=nothing set cs=nothing %> </outside_tag> </XML> <div id="xout"></DIV> <p> Click <a href="bdoc.asp?fl=x3"> here</a> for the source code </body> </html>
***** end of file *** printed: 99 lines on: 11/23/2009 5:59:13 AM
